DATETIME SELECT SELECT INTO DATE PAD STRING DYNAMIC SQL CURSOR MONEY FORMAT PERCENT STORED PROCEDURE SQL SERVER AGENT JOB OPTIMIZATION WHILE LOOP OVER PARTITION BY UPDATE
SITE SEARCH SQLUSA.com HEADLINES NEWS
SQL E/BOOKS  SQL 2012 PROGRAMMING  DOWNLOADS
SCRIPTS SQL 2005 SQL 2008 ARTICLES
SQL JOBS TWITTER FORMAT VIDEOS
How to construct a stored procedure in a schema?

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)

 

Exam Prep 70-461
Exam 70-461
DATETIME SELECT SELECT INTO DATE PAD STRING DYNAMIC SQL CURSOR MONEY FORMAT PERCENT STORED PROCEDURE SQL SERVER AGENT JOB OPTIMIZATION WHILE LOOP OVER PARTITION BY UPDATE