Function:
[AdventureWorks2016CTP3].[dbo].[ufnGetStock]
Function properties
| Name | Value |
| Schema | [dbo] |
| Owner | [dbo] |
| Creation date | 16.11.2015 |
| Type | Scalar |
| Is schema bound | |
| Is deterministic | |
| Encrypted | |
| Implementation type | Transact SQL |
| ID | 1437248175 |
Creation options
| Name | Value |
| QUOTED_IDENTIFIER | ON |
| ANSI_NULLS | ON |
Parameters
| Name | Description | DataType | Max length | ReadOnly |
| @ProductID | int | 4 |
Recordset returned
| Name | Datatype | Max length |
| @Return | int | 4 |
Objects that [dbo].[ufnGetStock] depends on
| Object name | Object type | Dep level |
| [Production].[ProductInventory] | Table | 1 |
Extended properties
| Name | Value |
| MS_Description | Scalar function returning the quantity of inventory in LocationID 6 (Miscellaneous Storage)for a specified ProductID. |
SQL
SET ANSI_NULLS ON SET QUOTED_IDENTIFIER 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 EXEC sp_addextendedproperty N'MS_Description', N'Scalar function returning the quantity of inventory in LocationID 6 (Miscellaneous Storage)for a specified ProductID.', 'SCHEMA', N'dbo', 'FUNCTION', N'ufnGetStock', NULL, NULL GO EXEC sp_addextendedproperty N'MS_Description', N'Input parameter for the scalar function ufnGetStock. Enter a valid ProductID from the Production.ProductInventory table.', 'SCHEMA', N'dbo', 'FUNCTION', N'ufnGetStock', 'PARAMETER', N'@ProductID' GO |
See also