Northwind Database (Document! X Sample)
dbo.ufnGetProductStandardCost User Defined Function
AdventureWorks Database > dbo Schema : dbo.ufnGetProductStandardCost User Defined Function
Description
Scalar function returning the standard cost for a given product on a particular order date.
Properties
Creation Date27/10/2017 14:33
Encrypted
Ansi Nulls
Parameters
ParameterDirectionDescriptionData TypeSize
InInput parameter for the scalar function ufnGetProductStandardCost. Enter a valid ProductID from the Production.Product table.Integer4
InInput parameter for the scalar function ufnGetProductStandardCost. Enter a valid order date.DBTimeStamp4
Return Value Currency8
Objects that dbo.ufnGetProductStandardCost depends on
 Database ObjectObject TypeDescriptionDep Level
dbo.Flag datatypedbo.FlagUser Defined Data Type 2
dbo.Name datatypedbo.NameUser Defined Data Type 2
Production.Product tableProduction.ProductTableProducts sold or used in the manfacturing of sold products.1
Production.ProductCategory tableProduction.ProductCategoryTableHigh-level product categorization.3
Production.ProductCostHistory tableProduction.ProductCostHistoryTableChanges in the cost of a product over time.1
Production.ProductModel tableProduction.ProductModelTableProduct model classification.2
Production.ProductSubcategory tableProduction.ProductSubcategoryTableProduct subcategories. See ProductCategory table.2
Production.UnitMeasure tableProduction.UnitMeasureTableUnit 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;
See Also

Related Objects

dbo Schema
AdventureWorks Database