dbo.ufnGetProductStandardCost User Defined Function

In This Topic

Description

Scalar function returning the standard cost for a given product on a particular order date.

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 ufnGetProductStandardCost. Enter a valid ProductID from the Production.Product table.	Integer	4
@OrderDate	In	Input parameter for the scalar function ufnGetProductStandardCost. Enter a valid order date.	DBTimeStamp	4
@RETURN_VALUE	Return Value		Currency	8

Objects that dbo.ufnGetProductStandardCost depends on

Database Object	Object Type	Description	Dep Level
dbo.Flag	User Defined Data Type		4
dbo.Name	User Defined Data Type		2
Production.Product	Table	Products sold or used in the manfacturing of sold products.	1
Production.ProductCategory	Table	High-level product categorization.	3
Production.ProductCostHistory	Table	Changes in the cost of a product over time.	2
Production.ProductModel	Table	Product model classification.	2
Production.ProductSubcategory	Table	Product subcategories. See ProductCategory table.	2
Production.UnitMeasure	Table	Unit of measure lookup table.	2

Procedure Source Code

CREATE FUNCTION [dbo].[ufnGetProductStandardCost](@ProductID [int], @OrderDate [datetime])
RETURNS [money] 
AS 

-- Returns the standard cost for the product on a specific date.
BEGIN
    DECLARE @StandardCost money;

    SELECT @StandardCost = pch.[StandardCost] 
    FROM [Production].[Product] p 
        INNER JOIN [Production].[ProductCostHistory] pch 
        ON p.[ProductID] = pch.[ProductID] 
            AND p.[ProductID] = @ProductID 
            AND @OrderDate BETWEEN pch.[StartDate] AND COALESCE(pch.[EndDate], CONVERT(datetime, '99991231', 112)); -- Make sure we get all the prices!

    RETURN @StandardCost;
END;

Related Objects

dbo Schema
AdventureWorks Database

Send comments on this topic.