Function: ufnGetProductDealerPrice

Properties

Property	Value
Name	ufnGetProductDealerPrice
ID	7671075
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 dealer price for a given product on a particular order date.

Permissions

Permissions for: JohnBlack

Permission	Grantor	Grant	With Grant Option	Deny
Alter	dbo
Control	dbo
Execute	dbo
References	dbo
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 ufnGetProductDealerPrice. Enter a valid ProductID from the Production.Product table.
@OrderDate	datetime					Input parameter for the scalar function ufnGetProductDealerPrice. Enter a valid order date.

Recordset Returned

Datatype	Has Default Value	Default Value	Xml Document	Description
money

Objects that depend on [ufnGetProductDealerPrice]

dbo.ufnGetProductDealerPrice
- Person.functionSynonym

Objects on which [ufnGetProductDealerPrice] depends

dbo.ufnGetProductDealerPrice
- Production.Product
- Production.ProductListPriceHistory

SQL

Script
SET ANSI_NULLS ON SET QUOTED_IDENTIFIER ON CREATE FUNCTION [dbo].[ufnGetProductDealerPrice](@ProductID [int], @OrderDate [datetime]) RETURNS [money] AS BEGIN DECLARE @DealerPrice money; DECLARE @DealerDiscount money; SET @DealerDiscount = 0.60 -- 60% of list price SELECT @DealerPrice = plph.[ListPrice] * @DealerDiscount FROM [Production].[Product] p INNER JOIN [Production].[ProductListPriceHistory] plph ON p.[ProductID] = plph.[ProductID] AND p.[ProductID] = @ProductID AND @OrderDate BETWEEN plph.[StartDate] AND COALESCE(plph.[EndDate], CONVERT(datetime, '99991231', 112)); -- Make sure we get all the prices! RETURN @DealerPrice; END; EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Input parameter for the scalar function ufnGetProductDealerPrice. Enter a valid ProductID from the Production.Product table.' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'FUNCTION',@level1name=N'ufnGetProductDealerPrice', @level2type=N'PARAMETER',@level2name=N'@ProductID' EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Input parameter for the scalar function ufnGetProductDealerPrice. Enter a valid order date.' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'FUNCTION',@level1name=N'ufnGetProductDealerPrice', @level2type=N'PARAMETER',@level2name=N'@OrderDate' EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Scalar function returning the dealer price for a given product on a particular order date.' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'FUNCTION',@level1name=N'ufnGetProductDealerPrice'

Script

SET ANSI_NULLS ON
SET QUOTED_IDENTIFIER ON
CREATE FUNCTION [dbo].[ufnGetProductDealerPrice](@ProductID [int], @OrderDate [datetime]) RETURNS [money] AS BEGIN DECLARE @DealerPrice money; DECLARE @DealerDiscount money; SET @DealerDiscount = 0.60 -- 60% of list price SELECT @DealerPrice = plph.[ListPrice] * @DealerDiscount FROM [Production].[Product] p INNER JOIN [Production].[ProductListPriceHistory] plph ON p.[ProductID] = plph.[ProductID] AND p.[ProductID] = @ProductID AND @OrderDate BETWEEN plph.[StartDate] AND COALESCE(plph.[EndDate], CONVERT(datetime, '99991231', 112)); -- Make sure we get all the prices! RETURN @DealerPrice; END;
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Input parameter for the scalar function ufnGetProductDealerPrice. Enter a valid ProductID from the Production.Product table.' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'FUNCTION',@level1name=N'ufnGetProductDealerPrice', @level2type=N'PARAMETER',@level2name=N'@ProductID'
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Input parameter for the scalar function ufnGetProductDealerPrice. Enter a valid order date.' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'FUNCTION',@level1name=N'ufnGetProductDealerPrice', @level2type=N'PARAMETER',@level2name=N'@OrderDate'
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Scalar function returning the dealer price for a given product on a particular order date.' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'FUNCTION',@level1name=N'ufnGetProductDealerPrice'