Function: dbo.ufnGetProductStandardCost

Properties

Owner:	dbo
Type:	SQL scalar function
Encrypted:
Creation Date:	04/26/2006
Modification Date:	04/26/2006
Description:	Scalar function returning the standard cost for a given product on a particular order date.

Creation options

QUOTED_IDENTIFIER:
ANSI_NULLS:

Parameters

Name	Direction	DataType	Length	Default	Description
@RETURN_VALUE	RETURN	money	8
@ProductID	INPUT	int	4		Input parameter for the scalar function ufnGetProductStandardCost. Enter a valid ProductID from the Production.Product table.
@OrderDate	INPUT	datetime	8		Input parameter for the scalar function ufnGetProductStandardCost. Enter a valid order date.

Total: 3 parameter(s)

Objects that [dbo].[ufnGetProductStandardCost] depends on

Object Name	Owner	Object Type	Dep Level
Flag	dbo	User Defined type	1
Name	dbo	User Defined type	1
ProductCategory	Production	Table	2
ProductModel	Production	Table	2
UnitMeasure	Production	Table	2
ProductSubcategory	Production	Table	3
Product	Production	Table	4
ProductCostHistory	Production	Table	5

Total: 8 object(s)

SQL

SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO

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;

GO
SET QUOTED_IDENTIFIER OFF
GO

GO
SET ANSI_NULLS OFF
GO