# 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
    - dbo.Flag
    - dbo.Name
    - Production.ProductModel
      - Production.ManuInstructionsSchemaCollection
      - Production.ProductDescriptionSchemaCollection
    - Production.ProductSubcategory
      - Production.ProductCategory
    - Production.UnitMeasure
  - 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'

#### See also

List of Functions