Properties

Owner:	dbo
Type:	SQL scalar function
Encrypted:
Creation Date:	04/26/2006
Modification Date:	04/26/2006
Description:	Scalar function returning the quantity of inventory in LocationID 6 (Miscellaneous Storage)for a specified ProductID.

Creation options

QUOTED_IDENTIFIER:
ANSI_NULLS:

Parameters

	Name	Direction	DataType	Length	Default	Description
	@RETURN_VALUE	RETURN	int	4
	@ProductID	INPUT	int	4		Input parameter for the scalar function ufnGetStock. Enter a valid ProductID from the Production.ProductInventory table.

Total: 2 parameter(s)

Objects that [dbo].[ufnGetStock] depends on

	Object Name	Owner	Object Type	Dep Level
	Flag	dbo	User Defined type	1
	Name	dbo	User Defined type	1
	Location	Production	Table	2
	ProductCategory	Production	Table	2
	ProductModel	Production	Table	2
	UnitMeasure	Production	Table	2
	ProductSubcategory	Production	Table	3
	Product	Production	Table	4
	ProductInventory	Production	Table	5

Total: 9 object(s)

SQL

SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO

CREATE FUNCTION [dbo].[ufnGetStock](@ProductID [int])
RETURNS [int]
AS
-- Returns the stock level for the product. This function is used internally only
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;

GO
SET QUOTED_IDENTIFIER OFF
GO

GO
SET ANSI_NULLS OFF
GO

See Also

List of functions