dbo.ufnGetStock User Defined Function

Description

Scalar function returning the quantity of inventory in LocationID 6 (Miscellaneous Storage)for a specified ProductID.

Properties

Creation Date	08/01/2010 08:41
Encrypted
Ansi Nulls

Parameters

Parameter	Direction	Description	Data Type	Size
@ProductID	In	Input parameter for the scalar function ufnGetStock. Enter a valid ProductID from the Production.ProductInventory table.	int	4
@RETURN_VALUE	Return Value		int	4

Objects that dbo.ufnGetStock depends on

Database Object	Object Type	Description	Dep Level
dbo.Flag	User Defined Data Type		3
Production.Location	Table	Product inventory and manufacturing locations.	2
dbo.Name	User Defined Data Type		3
Production.Product	Table	Products sold or used in the manfacturing of sold products.	2
Production.ProductCategory	Table	High-level product categorization.	4
Production.ProductInventory	Table	Product inventory information.	1
Production.ProductModel	Table	Product model classification.	3
Production.ProductSubcategory	Table	Product subcategories. See ProductCategory table.	3
Production.UnitMeasure	Table	Unit of measure lookup table.	3

Procedure Source Code

CREATE FUNCTION [dbo].[ufnGetStock](@ProductID [int])
RETURNS [int] 
AS 

BEGIN
DECLARE @ret int;

SELECT @ret = SUM(p.[Quantity]) 
FROM [Production].[ProductInventory] p 
WHERE p.[ProductID] = @ProductID 
AND p.[LocationID] = '6'; -- Only look at inventory in the misc storage

IF (@ret IS NULL) 
SET @ret = 0

RETURN @ret
END;

Related Objects

dbo Schema
AdventureWorks Database