dbo.ufnGetStock - AdventureWorks

AdventureWorks

- AdventureWorks

Documentation	AdventureWorks
Schema	dbo
Name	ufnGetStock
Module	Products

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

Input/Output

	Mode	Name	Data type	Description
1	OUT	Returns	int
2	IN	ProductID	int	Input parameter for the scalar function ufnGetStock. Enter a valid ProductID from the Production.ProductInventory table.
Input parameter for the scalar function ufnGetStock. Enter a valid ProductID from the Production.ProductInventory table.

Uses

Name
dbo.ufnGetStock
Production.ProductInventory

Script

CREATE FUNCTION [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;

CREATE FUNCTION [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;

Exported: 2019-02-04 23:13, Last imported: 2018-03-07 11:56