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	27/10/2017 14:33
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.	Integer	4
@RETURN_VALUE	Return Value		Integer	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 

-- 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;

Related Objects

dbo Schema
AdventureWorks Database