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 2014 PROGRAMMING   DOWNLOADS
SCRIPTS SQL 2005 SQL 2008 ARTICLES
SQL JOBS TWITTER FORMAT VIDEOS
How to architect stored procedure parameters?

Execute the following Microsoft SQL Server T-SQL scripts in Management Studio Query Editor to demonstrate the design of stored procedure parameters.

-- Stored procedure definition with input/output parameters - QUICK SYNTAX

USE AdventureWorks2008;

GO

CREATE PROCEDURE sprocSalesByQuarter

                @StartYear  INT,

                @TotalSales MONEY  OUTPUT

AS

  BEGIN -- sproc definition 

    SET NOCOUNT  ON -- turn off rows affected messages

    

    SELECT @TotalSales = SUM(SubTotal)

    FROM   Sales.SalesOrderHeader

    WHERE  OrderDate >= DATEADD(YY,@StartYear-1900,'19000101')

    

    SELECT   YEAR = YEAR(OrderDate),

             COALESCE(CONVERT(VARCHAR,SUM(CASE

                      WHEN DATEPART(QQ,OrderDate) = 1 THEN SubTotal

                                          END),1),'') AS 'Q1',

             COALESCE(CONVERT(VARCHAR,SUM(CASE

                      WHEN DATEPART(QQ,OrderDate) = 2 THEN SubTotal

                                          END),1),'') AS 'Q2',

             COALESCE(CONVERT(VARCHAR,SUM(CASE

                      WHEN DATEPART(QQ,OrderDate) = 3 THEN SubTotal

                                          END),1),'') AS 'Q3',

             COALESCE(CONVERT(VARCHAR,SUM(CASE

                      WHEN DATEPART(QQ,OrderDate) = 4 THEN SubTotal

                                          END),1),'') AS 'Q4'

    FROM     Sales.SalesOrderHeader soh

    WHERE    OrderDate >= DATEADD(YY,@StartYear-1900,'19000101')

    GROUP BY YEAR(OrderDate)

    ORDER BY YEAR(OrderDate);

    RETURN @@ROWCOUNT;

  END -- sproc definition

GO

-- Execute stored procedure with INPUT/OUTPUT parameters

DECLARE @TotSales money

EXEC sprocSalesByQuarter 2003, @TotSales OUTPUT

/* YEAR     Q1          Q2                Q3                Q4

2003  6,679,873.80      8,357,874.88      13,681,907.05     13,291,381.43

2004  11,398,376.28     14,379,545.19     50,840.63   */

SELECT 'Total Sales since 2003'=@TotSales

/* Total Sales since 2003

      67839799.2669  */

------------

-- T-SQL Stored Procedure with single input parameter - Basic Syntax

USE AdventureWorks2008;

GO

CREATE PROCEDURE uspProductByColor @pColor VARCHAR(32)

AS

  BEGIN

    SET nocount  ON

    IF @pColor IS NULL

      SELECT   Productid,

               ProductName = Name,

               Productnumber,

               ListPrice,

               Color

      FROM     Production.Product

      ORDER BY Color, Name

    ELSE

      SELECT   Productid,

               ProductName = Name,

               ProductNumber,

               ListPrice,

               Color

      FROM     Production.Product

      WHERE    Color = @pColor

      ORDER BY Color, Name

  END

GO

 

-- T-SQL execute stored procedure

EXEC uspProductByColor  'Yellow'

/* ProductID      Name                   ProductNumber 

797               Road-550-W Yellow, 38  BK-R64Y-38 

798               Road-550-W Yellow, 40  BK-R64Y-40 

799               Road-550-W Yellow, 42  BK-R64Y-42 

.... 

*/

------------ 

-- SQL stored procedure parameters

-- SQL stored procedure parameters default values - SQL Server stored procedure parameters

USE AdventureWorks;

GO

 

CREATE PROCEDURE uspVendorsByLocation

                @City    VARCHAR(30)  = NULL,

                @State   VARCHAR(30)  = NULL,

                @Country VARCHAR(50)  = NULL

AS

  BEGIN

    SELECT   V.VendorID,

             V.Name  AS Vendor,

             A.City,

             SP.Name AS State,

             CR.Name AS Country

    FROM     Purchasing.Vendor AS V

             JOIN Purchasing.VendorAddress AS VA

               ON VA.VendorID = V.VendorID

             JOIN Person.Address AS A

               ON A.AddressID = VA.AddressID

             JOIN Person.StateProvince AS SP

               ON SP.StateProvinceID = A.StateProvinceID

             JOIN Person.CountryRegion AS CR

               ON CR.CountryRegionCode = SP.CountryRegionCode

    WHERE    (A.City = @City

               OR @City IS NULL)

             AND (SP.Name = @State

                   OR @State IS NULL)

             AND (CR.Name = @Country

                   OR @Country IS NULL)

    ORDER BY Country,

             State,

             City,

             Vendor

  END

 

GO

 

-- Execute stored procedure by supplying all parameters

EXEC uspVendorsByLocation 'Los Angeles','California','United States'

-- Select from stored procedure

-- SQL Server openquery

SELECT * FROM OPENQUERY (DELLSTAR,

'EXEC AdventureWorks.dbo.uspVendorsByLocation ''Los Angeles'',

''California'',''United States''')

/*

VendorID    Vendor                  City        State       Country

27          Capital Road Cycles     Los Angeles California  United States

4           Comfort Road Bicycles   Los Angeles California  United States*/

*/

 

-- Execute stored procedure

-- Full list - no filtering

EXEC uspVendorsByLocation

SELECT * FROM OPENQUERY (DELLSTAR,

'EXEC AdventureWorks.dbo.uspVendorsByLocation')

 

-- (104 row(s) affected)

/* Partial results

VendorID    Vendor                        City        State       Country

88          Greenwood Athletic Company    Lemon Grove Arizona     United States

100         Holiday Skate & Cycle         Lemon Grove Arizona     United States

97          Northwind Traders             Phoenix     Arizona     United States

38          Allenson Cycles               Altadena    California  United States

48          Gardner Touring Cycles        Altadena    California  United States

*/

 

-- Execute stored procedure by supplying the City parameter

EXEC uspVendorsByLocation 'Berkeley'

EXEC uspVendorsByLocation Berkeley

SELECT * FROM OPENQUERY (DELLSTAR,

'EXEC AdventureWorks.dbo.uspVendorsByLocation ''Berkeley''')

 

/*

VendorID    Vendor                  City        State       Country

76          Cruger Bike Company     Berkeley    California  United States

98          Trikes, Inc.            Berkeley    California  United States

*/

 

-- Execute stored procedure by supplying the State parameter

EXEC uspVendorsByLocation NULL,'California'

SELECT * FROM OPENQUERY (DELLSTAR,

'EXEC AdventureWorks.dbo.uspVendorsByLocation NULL,''California''')

 

-- (39 row(s) affected)

 

-- Execute stored procedure by supplying the Country parameter

EXEC uspVendorsByLocation NULL,NULL,'United States'

SELECT * FROM OPENQUERY (DELLSTAR,

'EXEC AdventureWorks.dbo.uspVendorsByLocation NULL,NULL,''United States''')

 

-- (104 row(s) affected) -- entire set i.e. all vendors are U.S. based

------------ 

 

Related articles:


CREATE PROCEDURE (Transact-SQL)


Giving Permissions through Stored Procedures
Ownership Chaining, Certificates and the Problematic EXECUTE AS

 

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