|
Execute the following
Microsoft SQL Server T-SQL script in SSMS Query Editor to create a stored procedure and execute
it:
USE AdventureWorks2008;
GO
IF OBJECT_ID('Production.GetProductListBelowMaxPrice',
'P') IS NOT NULL
DROP PROCEDURE Production.GetProductListBelowMaxPrice;
GO
-- CREATE stored procedure with input/output parameters
-- The product parameter accepts wildcard like %touring%
CREATE PROCEDURE Production.GetProductListBelowMaxPrice
@Product VARCHAR(40),
@PriceLimit SMALLMONEY,
@Items INT OUTPUT,
@ListPrice SMALLMONEY OUT
AS
BEGIN
SELECT p.name AS Product,
p.ListPrice AS 'List Price'
FROM Production.Product p
JOIN Production.ProductSubcategory ps
ON p.ProductSubcategoryID = ps.ProductSubcategoryID
WHERE 1 = 1
AND (@Product IS NULL
OR p.[Name] LIKE @Product) -- NULL parm is OK
AND (@PriceLimit IS NULL
OR p.ListPrice <= @PriceLimit)-- NULL parm is OK
ORDER BY [List Price] DESC;
SET @Items = @@ROWCOUNT
SET @ListPrice = (SELECT MAX(p.ListPrice)
FROM Production.Product p
JOIN Production.ProductSubcategory ps
ON p.ProductSubcategoryID = ps.ProductSubcategoryID
WHERE ps.name LIKE @Product
AND p.ListPrice < @PriceLimit);
RETURN 1
END
GO
/*********** EXECUTION SCRIPT ***************/
DECLARE @RC INT
DECLARE @Product VARCHAR(40)
DECLARE @PriceLimit SMALLMONEY
DECLARE @Items INT
DECLARE @ListPrice SMALLMONEY
SET @Product = 'Road%'
SET @PriceLimit = 1000.0
EXECUTE @RC = [AdventureWorks2008].[Production].[GetProductListBelowMaxPrice]
@Product ,
@PriceLimit ,
@Items OUTPUT ,
@ListPrice OUTPUT
SELECT ReturnFlag = @RC,
Items = @Items,
LimitPrice = @PriceLimit,
MaxPriceReturned = @ListPrice
GO
Related link:
SQL Server Stored Procedure
(Introduction)
|
