Procedure: uspGetList

Properties

Property	Value
Name	uspGetList
ID	583673127
Schema	Production
Owner	dbo
Type	SQL STORED PROCEDURE
Execute As	Caller
Encrypted
ANSI NULLS
Uses Quoted Identifier
Published
Schema Published
Execution Replicated
Replication Serializable Only
Skips Replication Constraints
Is Recompiled
Uses Database Collation
Null on Null Input
MS Shipped
Modify Date	12/23/2010 6:58:48 AM
Create Date	12/23/2010 6:58:48 AM

Extended Properties

No extended properties defined.

Permissions

No object level permissions defined.

Parameters

Parameter Name	Datatype	Output	Cursor Ref	Readonly	Has Default Value	Default Value	Xml Collection Name	Description
@Product	varchar(40)
@MaxPrice	money
@ComparePrice	money
@ListPrice	money

Objects that depend on [uspGetList]

Production.uspGetList

Objects on which [uspGetList] depends

Production.uspGetList
- Production.Product

SQL

Script
SET ANSI_NULLS ON SET QUOTED_IDENTIFIER ON CREATE PROCEDURE Production.uspGetList @Product varchar(40) , @MaxPrice money , @ComparePrice money OUTPUT , @ListPrice money OUT AS SELECT p.[Name] AS Product, p.ListPrice AS 'List Price' FROM Production.Product AS p JOIN Production.ProductSubcategory AS s ON p.ProductSubcategoryID = s.ProductSubcategoryID WHERE s.[Name] LIKE @Product AND p.ListPrice < @MaxPrice; -- Populate the output variable @ListPprice. SET @ListPrice = (SELECT MAX(p.ListPrice) FROM Production.Product AS p JOIN Production.ProductSubcategory AS s ON p.ProductSubcategoryID = s.ProductSubcategoryID WHERE s.[Name] LIKE @Product AND p.ListPrice < @MaxPrice); -- Populate the output variable @compareprice. SET @ComparePrice = @MaxPrice;

Script

SET ANSI_NULLS ON
SET QUOTED_IDENTIFIER ON
CREATE PROCEDURE Production.uspGetList @Product varchar(40) , @MaxPrice money , @ComparePrice money OUTPUT , @ListPrice money OUT AS SELECT p.[Name] AS Product, p.ListPrice AS 'List Price' FROM Production.Product AS p JOIN Production.ProductSubcategory AS s ON p.ProductSubcategoryID = s.ProductSubcategoryID WHERE s.[Name] LIKE @Product AND p.ListPrice < @MaxPrice; -- Populate the output variable @ListPprice. SET @ListPrice = (SELECT MAX(p.ListPrice) FROM Production.Product AS p JOIN Production.ProductSubcategory AS s ON p.ProductSubcategoryID = s.ProductSubcategoryID WHERE s.[Name] LIKE @Product AND p.ListPrice < @MaxPrice); -- Populate the output variable @compareprice. SET @ComparePrice = @MaxPrice;