# Function: ufnGetStock

## Properties

Property | Value
--- | ---
Name | ufnGetStock
ID | 55671246
Schema | dbo
Owner | dbo
Execute As | Caller
Type | SQL SCALAR FUNCTION
Uses Ansi Nulls |
Uses Quoted Identifier |
Is Schema Bound |
Uses Database Collation |
Null on Null Input |
Encrypted |
MS Shipped |
Published |
Schema Published |
Deterministic |
Precise |
System Verified |
System Data Access |
User Data Access |
Modify Date | 8/8/2010 5:23:46 PM
Creation Date | 8/8/2010 5:23:46 PM

## Extended Properties

Name | Value
--- | ---
MS_Description | Scalar function returning the quantity of inventory in LocationID 6 (Miscellaneous Storage)for a specified ProductID.

## Permissions

#### Permissions for: duser

Permission | Grantor | Grant | With Grant Option | Deny
--- | --- | --- | --- | ---
Alter |  |  |  |
Control |  |  |  |
Execute | dbo |  |  |
References |  |  |  |
Take Ownership | dbo |  |  |
View Definition | dbo |  |  |

## Parameters

Parameter Name | Datatype | Has Default Value | Default Value | Read Only | Xml Document | Description
--- | --- | --- | --- | --- | --- | ---
@ProductID | int |  |  |  |  | Input parameter for the scalar function ufnGetStock. Enter a valid ProductID from the Production.ProductInventory table.

## Recordset Returned

Datatype | Has Default Value | Default Value | Xml Document | Description
--- | --- | --- | --- | ---
int |  |  |  |

## Objects that depend on [ufnGetStock]

- dbo.ufnGetStock

## Objects on which [ufnGetStock] depends

- dbo.ufnGetStock
  - Production.ProductInventory
    - Production.Location
      - dbo.Name
    - Production.Product
      - dbo.Flag
      - Production.ProductModel
        - Production.ManuInstructionsSchemaCollection
        - Production.ProductDescriptionSchemaCollection
      - Production.ProductSubcategory
        - Production.ProductCategory
      - Production.UnitMeasure

## SQL

Script
SET ANSI_NULLS ON SET QUOTED_IDENTIFIER ON CREATE FUNCTION [dbo].[ufnGetStock](@ProductID [int]) RETURNS [int] AS 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; EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Input parameter for the scalar function ufnGetStock. Enter a valid ProductID from the Production.ProductInventory table.' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'FUNCTION',@level1name=N'ufnGetStock', @level2type=N'PARAMETER',@level2name=N'@ProductID' EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Scalar function returning the quantity of inventory in LocationID 6 (Miscellaneous Storage)for a specified ProductID.' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'FUNCTION',@level1name=N'ufnGetStock'

#### See also

List of Functions