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. |
QUOTED_IDENTIFIER: | |
ANSI_NULLS: |
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. |
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 |
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