SQL Server/T-SQL Tutorial/Procedure Function/Parameter

Материал из SQL эксперт
Версия от 13:25, 26 мая 2010; Admin (обсуждение | вклад) (1 версия)
(разн.) ← Предыдущая | Текущая версия (разн.) | Следующая → (разн.)
Перейти к: навигация, поиск

Call procedure with parameter name

   <source lang="sql">

2> CREATE PROCEDURE pass_params 3> @param0 int=NULL, -- Defaults to NULL 4> @param1 int=1, -- Defaults to 1 5> @param2 int=2 -- Defaults to 2 6> AS 7> SELECT @param0, @param1, @param2 8> GO 1> 2> EXEC pass_params -- PASS NOTHING - ALL Defaults 3> GO

----------- ----------- 
      NULL           1           2

1> 2> EXEC pass_params 0, 10, 20 -- PASS ALL, IN ORDER 3> GO

----------- ----------- 
         0          10          20

1> 2> EXEC pass_params @param2=200, @param1=NULL 3> -- Explicitly identify last two params (out of order) 4> GO

----------- ----------- 
      NULL        NULL         200

1> 2> EXEC pass_params 0, DEFAULT, 20 3> -- Let param1 default. Others by position. 4> GO

----------- ----------- 
         0           1          20

1> 2> drop PROCEDURE pass_params ; 3> GO</source>


Check parameter value with if statement

   <source lang="sql">

3> CREATE FUNCTION fn_factorial (@param decimal(38, 0) ) 4> RETURNS decimal(38, 0) 5> 6> AS 7> BEGIN 8> IF (@param < 0 OR @param > 32) RETURN (0) 9> RETURN (CASE 10> WHEN @param > 1 THEN @param * dbo.fn_factorial(@param - 1) 11> ELSE 1 12> END) 13> END 14> GO 1> 2> SELECT factorial = dbo.fn_factorial(10) 3> GO factorial 

                                3628800</source>

Check value range for input parameter

   <source lang="sql">

5> 6> 7> CREATE TABLE Orders ( 8> OrderID int IDENTITY (1, 1) NOT NULL , 9> CustomerID nchar (5) NULL , 10> EmployeeID int NULL , 11> OrderDate datetime NULL , 12> RequiredDate datetime NULL , 13> ShippedDate datetime NULL , 14> ShipVia int NULL , 15> Freight money NULL DEFAULT (0), 16> ShipName nvarchar (40) NULL , 17> ShipAddress nvarchar (60) NULL , 18> ShipCity nvarchar (15) NULL , 19> ShipRegion nvarchar (15) NULL , 20> ShipPostalCode nvarchar (10) NULL , 21> ShipCountry nvarchar (15) NULL) 22> GO 1> 2> Create PROC spInsertDateValidatedOrder 3> @CustomerID nvarchar(5), 4> @EmployeeID int, 5> @OrderDate datetime = NULL, 6> @RequiredDate datetime = NULL, 7> @ShippedDate datetime = NULL, 8> @ShipVia int, 9> @Freight money, 10> @ShipName nvarchar(40) = NULL, 11> @ShipAddress nvarchar(60) = NULL, 12> @ShipCity nvarchar(15) = NULL, 13> @ShipRegion nvarchar(15) = NULL, 14> @ShipPostalCode nvarchar(10) = NULL, 15> @ShipCountry nvarchar(15) = NULL, 16> @OrderID int OUTPUT 17> AS 18> DECLARE @InsertedOrderDate smalldatetime 19> IF DATEDIFF(dd, @OrderDate, GETDATE()) > 7 20> BEGIN 21> SELECT @InsertedOrderDate = NULL 22> PRINT "Invalid Order Date" 23> PRINT "Supplied Order Date was greater than 7 days old." 24> PRINT "The value has been reset to NULL" 25> END 26> ELSE 27> BEGIN 28> SELECT @InsertedOrderDate = 29> CONVERT(datetime,(CONVERT(varchar,@OrderDate,112))) 30> PRINT "The Time of Day in Order Date was truncated" 31> END 32> INSERT INTO Orders VALUES(@CustomerID,@EmployeeID,@InsertedOrderDate,@RequiredDate, 33> @ShippedDate,@ShipVia,@Freight,@ShipName,@ShipAddress,@ShipCity,@ShipRegion, 34> @ShipPostalCode,@ShipCountry 35> ) 36> SELECT @OrderID = @@IDENTITY 37> GO 1> 2> DECLARE @MyIdent int 3> DECLARE @MyDate smalldatetime 4> 5> SELECT @MyDate = GETDATE() 6> 7> EXEC spInsertDateValidatedOrder 8> @CustomerID = "ALFKI", 9> @EmployeeID = 5, 10> @OrderDate = @MyDate, 11> @ShipVia = 3, 12> @Freight = 5.00, 13> @OrderID = @MyIdent OUTPUT 14> 15> SELECT OrderID, CustomerID, EmployeeID, OrderDate, ShipName 16> FROM Orders 17> WHERE OrderID = @MyIdent 18> GO The Time of Day in Order Date was truncated (1 rows affected) OrderID CustomerID EmployeeID OrderDate ShipName

---------- ----------- ----------------------- ---------------------------------------- 
         1 ALFKI                5 2008-08-18 00:00:00.000 NULL

(1 rows affected) 1> 2> 3> drop PROC spInsertDateValidatedOrder 4> GO 1> 2> drop table orders; 3> GO</source>


Code that omits both optional parameters

   <source lang="sql">

12> create table Billings ( 13> BankerID INTEGER, 14> BillingNumber INTEGER, 15> BillingDate datetime, 16> BillingTotal INTEGER, 17> TermsID INTEGER, 18> BillingDueDate datetime , 19> PaymentTotal INTEGER, 20> CreditTotal INTEGER 21> 22> ); 23> GO 1> 2> INSERT INTO Billings VALUES (1, 1, "2005-01-22", 165, 1,"2005-04-22",123,321); 3> GO (1 rows affected) 1> INSERT INTO Billings VALUES (2, 2, "2001-02-21", 165, 1,"2002-02-22",123,321.); 2> GO (1 rows affected) 1> INSERT INTO Billings VALUES (3, 3, "2003-05-02", 165, 1,"2005-04-12",123,321); 2> GO (1 rows affected) 1> INSERT INTO Billings VALUES (4, 4, "1999-03-12", 165, 1,"2005-04-18",123,321); 2> GO (1 rows affected) 1> INSERT INTO Billings VALUES (5, 5, "2000-04-23", 165, 1,"2005-04-17",123,321); 2> GO (1 rows affected) 1> INSERT INTO Billings VALUES (6, 6, "2001-06-14", 165, 1,"2005-04-18",123,321); 2> GO (1 rows affected) 1> INSERT INTO Billings VALUES (7, 7, "2002-07-15", 165, 1,"2005-04-19",123,321); 2> GO (1 rows affected) 1> INSERT INTO Billings VALUES (8, 8, "2003-08-16", 165, 1,"2005-04-20",123,321); 2> GO (1 rows affected) 1> INSERT INTO Billings VALUES (9, 9, "2004-09-17", 165, 1,"2005-04-21",123,321); 2> GO (1 rows affected) 1> INSERT INTO Billings VALUES (0, 0, "2005-10-18", 165, 1,"2005-04-22",123,321); 2> GO (1 rows affected) 1> 2> 3> create table Bankers( 4> BankerID Integer, 5> BankerName VARCHAR(20), 6> BankerContactLName VARCHAR(20), 7> BankerContactFName VARCHAR(20), 8> BankerCity VARCHAR(20), 9> BankerState VARCHAR(20), 10> BankerZipCode VARCHAR(20), 11> BankerPhone VARCHAR(20) 12> ) 13> GO 1> 2> insert into Bankers values (1, "ABC Inc.","Joe","Smith","Vancouver","BC","11111","111-111-1111"); 3> GO (1 rows affected) 1> insert into Bankers values (2, "DEF Inc.","Red","Rice", "New York", "DE","22222","222-222-2222"); 2> GO (1 rows affected) 1> insert into Bankers values (3, "HJI Inc.","Kit","Cat", "Paris", "CA","33333","333-333-3333"); 2> GO (1 rows affected) 1> insert into Bankers values (4, "QWE Inc.","Git","Black","Regina", "ER","44444","444-444-4444"); 2> GO (1 rows affected) 1> insert into Bankers values (5, "RTY Inc.","Wil","Lee", "Toronto", "YU","55555","555-555-5555"); 2> GO (1 rows affected) 1> insert into Bankers values (6, "YUI Inc.","Ted","Larry","Calgary", "TY","66666","666-666-6666"); 2> GO (1 rows affected) 1> insert into Bankers values (7, "OIP Inc.","Yam","Act", "San Franc","FG","77777","777-777-7777"); 2> GO (1 rows affected) 1> insert into Bankers values (8, "SAD Inc.","Hit","Eat", "Orland", "PO","88888","888-888-8888"); 2> GO (1 rows affected) 1> insert into Bankers values (9, "DFG Inc.","Sad","Lee", "Wisler", "PL","99999","999-999-9999"); 2> GO (1 rows affected) 1> insert into Bankers values (0, "GHJ Inc.","Bit","Lee", "Ticker", "MN","00000","000-000-0000"); 2> GO (1 rows affected) 1> 2> CREATE PROC spInvTotal3 3> @InvTotal money OUTPUT, 4> @DateVar smalldatetime = NULL, 5> @BankerVar varchar(40) = "%" 6> AS 7> IF @DateVar IS NULL 8> SELECT @DateVar = MIN(BillingDate) FROM Billings 9> 10> SELECT @InvTotal = SUM(BillingTotal) 11> FROM Billings JOIN Bankers 12> ON Billings.BankerID = Bankers.BankerID 13> WHERE (BillingDate >= @DateVar) AND 14> (BankerName LIKE @BankerVar) 15> GO 1> 2> DECLARE @MyInvTotal money 3> EXEC spInvTotal3 @MyInvTotal OUTPUT 4> GO 1> 2> drop PROC spInvTotal3; 3> GO 1> 2> 3> drop table Billings; 4> GO 1> 2> drop table Bankers; 3> GO 1> 2> 3></source>


Code that omits one optional parameter

   <source lang="sql">

7> 8> 9> 10> create table Billings ( 11> BankerID INTEGER, 12> BillingNumber INTEGER, 13> BillingDate datetime, 14> BillingTotal INTEGER, 15> TermsID INTEGER, 16> BillingDueDate datetime , 17> PaymentTotal INTEGER, 18> CreditTotal INTEGER 19> 20> ); 21> GO 1> 2> INSERT INTO Billings VALUES (1, 1, "2005-01-22", 165, 1,"2005-04-22",123,321); 3> GO (1 rows affected) 1> INSERT INTO Billings VALUES (2, 2, "2001-02-21", 165, 1,"2002-02-22",123,321.); 2> GO (1 rows affected) 1> INSERT INTO Billings VALUES (3, 3, "2003-05-02", 165, 1,"2005-04-12",123,321); 2> GO (1 rows affected) 1> INSERT INTO Billings VALUES (4, 4, "1999-03-12", 165, 1,"2005-04-18",123,321); 2> GO (1 rows affected) 1> INSERT INTO Billings VALUES (5, 5, "2000-04-23", 165, 1,"2005-04-17",123,321); 2> GO (1 rows affected) 1> INSERT INTO Billings VALUES (6, 6, "2001-06-14", 165, 1,"2005-04-18",123,321); 2> GO (1 rows affected) 1> INSERT INTO Billings VALUES (7, 7, "2002-07-15", 165, 1,"2005-04-19",123,321); 2> GO (1 rows affected) 1> INSERT INTO Billings VALUES (8, 8, "2003-08-16", 165, 1,"2005-04-20",123,321); 2> GO (1 rows affected) 1> INSERT INTO Billings VALUES (9, 9, "2004-09-17", 165, 1,"2005-04-21",123,321); 2> GO (1 rows affected) 1> INSERT INTO Billings VALUES (0, 0, "2005-10-18", 165, 1,"2005-04-22",123,321); 2> GO (1 rows affected) 1> 2> 3> create table Bankers( 4> BankerID Integer, 5> BankerName VARCHAR(20), 6> BankerContactLName VARCHAR(20), 7> BankerContactFName VARCHAR(20), 8> BankerCity VARCHAR(20), 9> BankerState VARCHAR(20), 10> BankerZipCode VARCHAR(20), 11> BankerPhone VARCHAR(20) 12> ) 13> GO 1> 2> insert into Bankers values (1, "ABC Inc.","Joe","Smith","Vancouver","BC","11111","111-111-1111"); 3> GO (1 rows affected) 1> insert into Bankers values (2, "DEF Inc.","Red","Rice", "New York", "DE","22222","222-222-2222"); 2> GO (1 rows affected) 1> insert into Bankers values (3, "HJI Inc.","Kit","Cat", "Paris", "CA","33333","333-333-3333"); 2> GO (1 rows affected) 1> insert into Bankers values (4, "QWE Inc.","Git","Black","Regina", "ER","44444","444-444-4444"); 2> GO (1 rows affected) 1> insert into Bankers values (5, "RTY Inc.","Wil","Lee", "Toronto", "YU","55555","555-555-5555"); 2> GO (1 rows affected) 1> insert into Bankers values (6, "YUI Inc.","Ted","Larry","Calgary", "TY","66666","666-666-6666"); 2> GO (1 rows affected) 1> insert into Bankers values (7, "OIP Inc.","Yam","Act", "San Franc","FG","77777","777-777-7777"); 2> GO (1 rows affected) 1> insert into Bankers values (8, "SAD Inc.","Hit","Eat", "Orland", "PO","88888","888-888-8888"); 2> GO (1 rows affected) 1> insert into Bankers values (9, "DFG Inc.","Sad","Lee", "Wisler", "PL","99999","999-999-9999"); 2> GO (1 rows affected) 1> insert into Bankers values (0, "GHJ Inc.","Bit","Lee", "Ticker", "MN","00000","000-000-0000"); 2> GO (1 rows affected) 1> 2> CREATE PROC spInvTotal3 3> @InvTotal money OUTPUT, 4> @DateVar smalldatetime = NULL, 5> @BankerVar varchar(40) = "%" 6> AS 7> IF @DateVar IS NULL 8> SELECT @DateVar = MIN(BillingDate) FROM Billings 9> 10> SELECT @InvTotal = SUM(BillingTotal) 11> FROM Billings JOIN Bankers 12> ON Billings.BankerID = Bankers.BankerID 13> WHERE (BillingDate >= @DateVar) AND 14> (BankerName LIKE @BankerVar) 15> GO 1> 2> 3> 4> DECLARE @MyInvTotal money 5> 6> EXEC spInvTotal3 @BankerVar = "M%", @InvTotal = @MyInvTotal OUTPUT 7> 8> GO 1> 2> drop PROC spInvTotal3; 3> GO 1> 2> 3> drop table Billings; 4> GO 1> 2> drop table Bankers; 3> GO 1> 2> 3></source>


Code that passes the parameters by name

   <source lang="sql">

6> 7> 8> create table Billings ( 9> BankerID INTEGER, 10> BillingNumber INTEGER, 11> BillingDate datetime, 12> BillingTotal INTEGER, 13> TermsID INTEGER, 14> BillingDueDate datetime , 15> PaymentTotal INTEGER, 16> CreditTotal INTEGER 17> 18> ); 19> GO 1> 2> INSERT INTO Billings VALUES (1, 1, "2005-01-22", 165, 1,"2005-04-22",123,321); 3> GO (1 rows affected) 1> INSERT INTO Billings VALUES (2, 2, "2001-02-21", 165, 1,"2002-02-22",123,321.); 2> GO (1 rows affected) 1> INSERT INTO Billings VALUES (3, 3, "2003-05-02", 165, 1,"2005-04-12",123,321); 2> GO (1 rows affected) 1> INSERT INTO Billings VALUES (4, 4, "1999-03-12", 165, 1,"2005-04-18",123,321); 2> GO (1 rows affected) 1> INSERT INTO Billings VALUES (5, 5, "2000-04-23", 165, 1,"2005-04-17",123,321); 2> GO (1 rows affected) 1> INSERT INTO Billings VALUES (6, 6, "2001-06-14", 165, 1,"2005-04-18",123,321); 2> GO (1 rows affected) 1> INSERT INTO Billings VALUES (7, 7, "2002-07-15", 165, 1,"2005-04-19",123,321); 2> GO (1 rows affected) 1> INSERT INTO Billings VALUES (8, 8, "2003-08-16", 165, 1,"2005-04-20",123,321); 2> GO (1 rows affected) 1> INSERT INTO Billings VALUES (9, 9, "2004-09-17", 165, 1,"2005-04-21",123,321); 2> GO (1 rows affected) 1> INSERT INTO Billings VALUES (0, 0, "2005-10-18", 165, 1,"2005-04-22",123,321); 2> GO (1 rows affected) 1> 2> 3> create table Bankers( 4> BankerID Integer, 5> BankerName VARCHAR(20), 6> BankerContactLName VARCHAR(20), 7> BankerContactFName VARCHAR(20), 8> BankerCity VARCHAR(20), 9> BankerState VARCHAR(20), 10> BankerZipCode VARCHAR(20), 11> BankerPhone VARCHAR(20) 12> ) 13> GO 1> 2> insert into Bankers values (1, "ABC Inc.","Joe","Smith","Vancouver","BC","11111","111-111-1111"); 3> GO (1 rows affected) 1> insert into Bankers values (2, "DEF Inc.","Red","Rice", "New York", "DE","22222","222-222-2222"); 2> GO (1 rows affected) 1> insert into Bankers values (3, "HJI Inc.","Kit","Cat", "Paris", "CA","33333","333-333-3333"); 2> GO (1 rows affected) 1> insert into Bankers values (4, "QWE Inc.","Git","Black","Regina", "ER","44444","444-444-4444"); 2> GO (1 rows affected) 1> insert into Bankers values (5, "RTY Inc.","Wil","Lee", "Toronto", "YU","55555","555-555-5555"); 2> GO (1 rows affected) 1> insert into Bankers values (6, "YUI Inc.","Ted","Larry","Calgary", "TY","66666","666-666-6666"); 2> GO (1 rows affected) 1> insert into Bankers values (7, "OIP Inc.","Yam","Act", "San Franc","FG","77777","777-777-7777"); 2> GO (1 rows affected) 1> insert into Bankers values (8, "SAD Inc.","Hit","Eat", "Orland", "PO","88888","888-888-8888"); 2> GO (1 rows affected) 1> insert into Bankers values (9, "DFG Inc.","Sad","Lee", "Wisler", "PL","99999","999-999-9999"); 2> GO (1 rows affected) 1> insert into Bankers values (0, "GHJ Inc.","Bit","Lee", "Ticker", "MN","00000","000-000-0000"); 2> GO (1 rows affected) 1> 2> CREATE PROC spInvTotal3 3> @InvTotal money OUTPUT, 4> @DateVar smalldatetime = NULL, 5> @BankerVar varchar(40) = "%" 6> AS 7> IF @DateVar IS NULL 8> SELECT @DateVar = MIN(BillingDate) FROM Billings 9> 10> SELECT @InvTotal = SUM(BillingTotal) 11> FROM Billings JOIN Bankers 12> ON Billings.BankerID = Bankers.BankerID 13> WHERE (BillingDate >= @DateVar) AND 14> (BankerName LIKE @BankerVar) 15> GO 1> 2> 3> DECLARE @MyInvTotal money 4> EXEC spInvTotal3 @DateVar = "2002-06-01", @BankerVar = "P%", 5> @InvTotal = @MyInvTotal OUTPUT 6> GO 1> 2> drop PROC spInvTotal3; 3> GO 1> 2> 3> drop table Billings; 4> GO 1> 2> drop table Bankers; 3> GO 1> 2> 3></source>


Code that passes the parameters by position

   <source lang="sql">

9> create table Billings ( 10> BankerID INTEGER, 11> BillingNumber INTEGER, 12> BillingDate datetime, 13> BillingTotal INTEGER, 14> TermsID INTEGER, 15> BillingDueDate datetime , 16> PaymentTotal INTEGER, 17> CreditTotal INTEGER 18> 19> ); 20> GO 1> 2> INSERT INTO Billings VALUES (1, 1, "2005-01-22", 165, 1,"2005-04-22",123,321); 3> GO (1 rows affected) 1> INSERT INTO Billings VALUES (2, 2, "2001-02-21", 165, 1,"2002-02-22",123,321.); 2> GO (1 rows affected) 1> INSERT INTO Billings VALUES (3, 3, "2003-05-02", 165, 1,"2005-04-12",123,321); 2> GO (1 rows affected) 1> INSERT INTO Billings VALUES (4, 4, "1999-03-12", 165, 1,"2005-04-18",123,321); 2> GO (1 rows affected) 1> INSERT INTO Billings VALUES (5, 5, "2000-04-23", 165, 1,"2005-04-17",123,321); 2> GO (1 rows affected) 1> INSERT INTO Billings VALUES (6, 6, "2001-06-14", 165, 1,"2005-04-18",123,321); 2> GO (1 rows affected) 1> INSERT INTO Billings VALUES (7, 7, "2002-07-15", 165, 1,"2005-04-19",123,321); 2> GO (1 rows affected) 1> INSERT INTO Billings VALUES (8, 8, "2003-08-16", 165, 1,"2005-04-20",123,321); 2> GO (1 rows affected) 1> INSERT INTO Billings VALUES (9, 9, "2004-09-17", 165, 1,"2005-04-21",123,321); 2> GO (1 rows affected) 1> INSERT INTO Billings VALUES (0, 0, "2005-10-18", 165, 1,"2005-04-22",123,321); 2> GO (1 rows affected) 1> 2> 3> create table Bankers( 4> BankerID Integer, 5> BankerName VARCHAR(20), 6> BankerContactLName VARCHAR(20), 7> BankerContactFName VARCHAR(20), 8> BankerCity VARCHAR(20), 9> BankerState VARCHAR(20), 10> BankerZipCode VARCHAR(20), 11> BankerPhone VARCHAR(20) 12> ) 13> GO 1> 2> insert into Bankers values (1, "ABC Inc.","Joe","Smith","Vancouver","BC","11111","111-111-1111"); 3> GO (1 rows affected) 1> insert into Bankers values (2, "DEF Inc.","Red","Rice", "New York", "DE","22222","222-222-2222"); 2> GO (1 rows affected) 1> insert into Bankers values (3, "HJI Inc.","Kit","Cat", "Paris", "CA","33333","333-333-3333"); 2> GO (1 rows affected) 1> insert into Bankers values (4, "QWE Inc.","Git","Black","Regina", "ER","44444","444-444-4444"); 2> GO (1 rows affected) 1> insert into Bankers values (5, "RTY Inc.","Wil","Lee", "Toronto", "YU","55555","555-555-5555"); 2> GO (1 rows affected) 1> insert into Bankers values (6, "YUI Inc.","Ted","Larry","Calgary", "TY","66666","666-666-6666"); 2> GO (1 rows affected) 1> insert into Bankers values (7, "OIP Inc.","Yam","Act", "San Franc","FG","77777","777-777-7777"); 2> GO (1 rows affected) 1> insert into Bankers values (8, "SAD Inc.","Hit","Eat", "Orland", "PO","88888","888-888-8888"); 2> GO (1 rows affected) 1> insert into Bankers values (9, "DFG Inc.","Sad","Lee", "Wisler", "PL","99999","999-999-9999"); 2> GO (1 rows affected) 1> insert into Bankers values (0, "GHJ Inc.","Bit","Lee", "Ticker", "MN","00000","000-000-0000"); 2> GO (1 rows affected) 1> 2> CREATE PROC spInvTotal3 3> @InvTotal money OUTPUT, 4> @DateVar smalldatetime = NULL, 5> @BankerVar varchar(40) = "%" 6> AS 7> IF @DateVar IS NULL 8> SELECT @DateVar = MIN(BillingDate) FROM Billings 9> 10> SELECT @InvTotal = SUM(BillingTotal) 11> FROM Billings JOIN Bankers 12> ON Billings.BankerID = Bankers.BankerID 13> WHERE (BillingDate >= @DateVar) AND 14> (BankerName LIKE @BankerVar) 15> GO 1> DECLARE @MyInvTotal money 2> EXEC spInvTotal3 @MyInvTotal OUTPUT, "2002-06-01", "P%" 3> GO 1> 2> drop PROC spInvTotal3; 3> GO 1> 2> 3> drop table Billings; 4> GO 1> 2> drop table Bankers; 3> GO 1> 2> 3></source>


Parameterization: @parameter_name [AS] datatype [= default|NULL] [VARYING] [OUTPUT|OUT]

   <source lang="sql">

10> 11> 12> CREATE TABLE Shippers ( 13> ShipperID int NOT NULL , 14> CompanyName nvarchar (40) NOT NULL , 15> Phone nvarchar (24) NULL 16> ) 17> GO 1> 2> 3> INSERT Shippers VALUES(1,"Express","(503) 555-9831") 4> INSERT Shippers VALUES(2,"Package","(503) 555-3199") 5> INSERT Shippers VALUES(3,"Shipping","(503) 555-9931") 6> go (1 rows affected) (1 rows affected) (1 rows affected) 1> CREATE PROC spInsertShipper 2> @CompanyName nvarchar(40), 3> @Phone nvarchar(24) 4> AS 5> INSERT INTO Shippers (CompanyName,Phone ) 6> VALUES 7> (@CompanyName, @Phone) 8> 9> GO 1> 2> EXEC spInsertShipper "Speedy Shippers, Inc.", "(503) 555-5566" 3> GO Msg 515, Level 16, State 2, Server J\SQLEXPRESS, Procedure spInsertShipper, Line 5 Cannot insert the value NULL into column "ShipperID", table "master.dbo.Shippers"; column does not allow nulls. INSERT fails. The statement has been terminated. 1> 2> drop PROC spInsertShipper; 3> drop table Shippers; 4> GO</source>


parameters can be passed explicitly by value

   <source lang="sql">

2> 3> CREATE TABLE Product( 4> ProductID int NOT NULL, 5> Name nvarchar(25) NOT NULL, 6> ProductNumber nvarchar(25) , 7> Color nvarchar(15) NULL, 8> StandardCost money NOT NULL, 9> Size nvarchar(5) NULL, 10> Weight decimal(8, 2) NULL, 11> ProductLine nchar(20) NULL, 12> SellStartDate datetime NOT NULL, 13> SellEndDate datetime NULL 14> ) 15> GO 1> insert into Product values(1,"Product A", "1","Red",123.123,"1",1,"ProductLine A","1999-03-22","2000-03-22"); 2> GO (1 rows affected) 1> 2> 3> insert into Product values(2,"Product B", "2","Yellow",234.234,"1",3,"ProductLine B","2000-03-22","2001-03-22"); 4> GO (1 rows affected) 1> 2> 3> insert into Product values(3,"Product C", "3","Pink",345.345,"1",3,"ProductLine V","2001-09-22","2006-02-22"); 4> GO (1 rows affected) 1> 2> 3> insert into Product values(4,"Product D", "4","White",456.456,"1",4,"ProductLine D","2002-08-22","2006-03-22"); 4> GO (1 rows affected) 1> 2> 3> insert into Product values(5,"Product E", "5","Black",567.567,"1",5,"ProductLine E","2003-01-22","2003-04-22"); 4> GO (1 rows affected) 1> 2> 3> insert into Product values(6,"Product F", "6","Blue",678.678,"1",6,"ProductLine W","2004-02-22","2005-05-22"); 4> GO (1 rows affected) 1> 2> 3> insert into Product values(7,"Product G", "7","Drak",789.789,"1",7,"ProductLine Q","2005-03-22","2006-03-22"); 4> GO (1 rows affected) 1> 2> 3> insert into Product values(8,"Product H", "8","Gray",234.123,"1",8,"ProductLine F","2006-04-22","2006-09-22"); 4> GO (1 rows affected) 1> 2> 3> insert into Product values(9,"Product I", "9","Red",543.123,"1",9,"ProductLine R","2007-05-22","2008-03-22"); 4> GO (1 rows affected) 1> 2> 3> insert into Product values(0,"Product J", "0","Gold",765.123,"1",0,"ProductLine J","2008-06-22","2009-03-22"); 4> GO (1 rows affected) 1> 2> 3> 4> 5> CREATE PROCEDURE spEmployeeByCost 6> @SubID Int, @Cost Money 7> AS 8> SELECT ProductID, Name, ProductNumber, StandardCost 9> FROM Product 10> WHERE ProductID = @SubID 11> AND StandardCost > @Cost 12> GO 1> 2> 3> EXECUTE spEmployeeByCost 1, $1000.00 4> GO ProductID Name ProductNumber StandardCost

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

(0 rows affected) 1> 2> --Or the parameters can be passed explicitly by value. If the parameters are supplied by value it doesn"t matter in what order they are supplied: 3> EXECUTE spEmployeeByCost @Cost = $1000.00, @SubID = 1 4> GO ProductID Name ProductNumber StandardCost

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

(0 rows affected) 1> drop table Product; 2> GO</source>


Parameter with null default value

   <source lang="sql">

3> 4> CREATE TABLE titles( 5> title_id varchar(20), 6> title varchar(80) NOT NULL, 7> type char(12) NOT NULL, 8> pub_id char(4) NULL, 9> price money NULL, 10> advance money NULL, 11> royalty int NULL, 12> ytd_sales int NULL, 13> notes varchar(200) NULL, 14> pubdate datetime NOT NULL 15> ) 16> GO 1> 2> insert titles values ("1", "Secrets", "popular_comp", "1389", $20.00, $8000.00, 10, 4095,"Note 1","06/12/94") 3> insert titles values ("2", "The", "business", "1389", $19.99, $5000.00, 10, 4095,"Note 2","06/12/91") 4> insert titles values ("3", "Emotional", "psychology", "0736", $7.99, $4000.00, 10, 3336,"Note 3","06/12/91") 5> insert titles values ("4", "Prolonged", "psychology", "0736", $19.99, $2000.00, 10, 4072,"Note 4","06/12/91") 6> insert titles values ("5", "With", "business", "1389", $11.95, $5000.00, 10, 3876,"Note 5","06/09/91") 7> insert titles values ("6", "Valley", "mod_cook", "0877", $19.99, $0.00, 12, 2032,"Note 6","06/09/91") 8> insert titles values ("7", "Any?", "trad_cook", "0877", $14.99, $8000.00, 10, 4095,"Note 7","06/12/91") 9> insert titles values ("8", "Fifty", "trad_cook", "0877", $11.95, $4000.00, 14, 1509,"Note 8","06/12/91") 10> GO 1> 2> 3> CREATE PROC gettitle_by_price 4> @cost money = NULL 5> AS 6> SELECT price, title 7> FROM titles 8> WHERE price = ISNULL(@cost, price) 9> RETURN 10> GO 1> 2> drop table titles; 3> GO 1> 2> drop PROC gettitle_by_price; 3> GO</source>


Pass column as the parameter

   <source lang="sql">

6> CREATE TABLE titles( 7> title_id varchar(20), 8> title varchar(80) NOT NULL, 9> type char(12) NOT NULL, 10> pub_id char(4) NULL, 11> price money NULL, 12> advance money NULL, 13> royalty int NULL, 14> ytd_sales int NULL, 15> notes varchar(200) NULL, 16> pubdate datetime NOT NULL 17> ) 18> GO 1> 2> insert titles values ("1", "Secrets", "popular_comp", "1389", $20.00, $8000.00, 10, 4095,"Note 1","06/12/94") 3> insert titles values ("2", "The", "business", "1389", $19.99, $5000.00, 10, 4095,"Note 2","06/12/91") 4> insert titles values ("3", "Emotional", "psychology", "0736", $7.99, $4000.00, 10, 3336,"Note 3","06/12/91") 5> insert titles values ("4", "Prolonged", "psychology", "0736", $19.99, $2000.00, 10, 4072,"Note 4","06/12/91") 6> insert titles values ("5", "With", "business", "1389", $11.95, $5000.00, 10, 3876,"Note 5","06/09/91") 7> insert titles values ("6", "Valley", "mod_cook", "0877", $19.99, $0.00, 12, 2032,"Note 6","06/09/91") 8> insert titles values ("7", "Any?", "trad_cook", "0877", $14.99, $8000.00, 10, 4095,"Note 7","06/12/91") 9> insert titles values ("8", "Fifty", "trad_cook", "0877", $11.95, $4000.00, 14, 1509,"Note 8","06/12/91") 10> GO (1 rows affected) (1 rows affected) (1 rows affected) (1 rows affected) (1 rows affected) (1 rows affected) (1 rows affected) (1 rows affected) 1> CREATE FUNCTION dbo.AveragePrice() 2> RETURNS money 3> WITH SCHEMABINDING 4> AS 5> BEGIN 6> RETURN (SELECT AVG(Price) FROM dbo.Titles) 7> END 8> GO 1> 2> CREATE FUNCTION dbo.PriceDifference(@Price money) 3> RETURNS money 4> AS 5> BEGIN 6> RETURN @Price - dbo.AveragePrice() 7> END 8> GO 1> SELECT Title, 2> Price, 3> dbo.AveragePrice() AS Average, 4> dbo.PriceDifference(Price) AS Difference 5> FROM Titles 6> WHERE Type="popular_comp" 7> GO Title Price Average Difference

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

Secrets 20.0000 15.8562 4.1438 Secrets 20.0000 15.8562 4.1438 Secrets 20.0000 15.8562 4.1438 Secrets 20.0000 15.8562 4.1438 Secrets 20.0000 15.8562 4.1438 Secrets 20.0000 15.8562 4.1438 Secrets 20.0000 15.8562 4.1438 (7 rows affected) 1></source>


Passing the ORDER BY Column as a Parameter, Using a Column Number

   <source lang="sql">

4> 5> CREATE TABLE authors( 6> au_id varchar(11), 7> au_lname varchar(40) NOT NULL, 8> au_fname varchar(20) NOT NULL, 9> phone char(12) NOT NULL DEFAULT ("UNKNOWN"), 10> address varchar(40) NULL, 11> city varchar(20) NULL, 12> state char(2) NULL, 13> zip char(5) NULL, 14> contract bit NOT NULL 15> ) 16> GO 1> insert authors values("1", "Joe", "Abra", "111 111-1111", "6 St.", "Berkeley", "CA", "11111", 1) 2> insert authors values("2", "Jack", "Majo", "222 222-2222", "3 St.", "Oakland" , "CA", "22222", 1) 3> insert authors values("3", "Pink", "Cherry", "333 333-3333", "5 Ln.", "Vancouver", "BC", "33333", 1) 4> insert authors values("4", "Blue", "Albert", "444 444-4444", "7 Av.", "Vancouver", "BC", "44444", 1) 5> insert authors values("5", "Red", "Anne", "555 555-5555", "6 Av.", "Regina", "SK", "55555", 1) 6> insert authors values("6", "Black", "Michel", "666 666-6666", "3 Pl.", "Regina", "SK", "66666", 1) 7> insert authors values("7", "White", "Sylvia", "777 777-7777", "1 Pl.", "Rockville", "MD", "77777", 1) 8> insert authors values("8", "Yellow","Heather","888 888-8888", "3 Pu", "Vacaville", "CA", "88888", 0) 9> insert authors values("9", "Gold", "Dep", "999 999-9999", "5 Av.", "Oakland", "CA", "99999", 0) 10> insert authors values("10", "Siler", "Dean", "000 000-0000", "4 Av.", "Oakland", "CA", "00000", 1) 11> GO (1 rows affected) (1 rows affected) (1 rows affected) (1 rows affected) (1 rows affected) (1 rows affected) (1 rows affected) (1 rows affected) (1 rows affected) (1 rows affected) 1> 2> 3> 4> Create PROC GetAuthors1 5> @colnum AS int 6> AS 7> SELECT 8> * 9> FROM 10> authors 11> ORDER BY 12> CASE @colnum 13> WHEN 1 THEN au_id 14> WHEN 2 THEN au_lname 15> WHEN 3 THEN au_fname 16> WHEN 4 THEN phone 17> WHEN 5 THEN address 18> WHEN 6 THEN city 19> WHEN 7 THEN state 20> WHEN 8 THEN zip 21> WHEN 9 THEN CAST(contract AS CHAR(1)) 22> ELSE NULL 23> END 24> GO 1> 2> 3> drop proc GetAuthors1; 4> GO 1> 2> drop table authors; 3> GO 1></source>


Passing the ORDER BY Column as a Parameter, Using Dynamic Execution

   <source lang="sql">

9> 10> 11> CREATE TABLE authors( 12> au_id varchar(11), 13> au_lname varchar(40) NOT NULL, 14> au_fname varchar(20) NOT NULL, 15> phone char(12) NOT NULL DEFAULT ("UNKNOWN"), 16> address varchar(40) NULL, 17> city varchar(20) NULL, 18> state char(2) NULL, 19> zip char(5) NULL, 20> contract bit NOT NULL 21> ) 22> GO 1> insert authors values("1", "Joe", "Abra", "111 111-1111", "6 St.", "Berkeley", "CA", "11111", 1) 2> insert authors values("2", "Jack", "Majo", "222 222-2222", "3 St.", "Oakland" , "CA", "22222", 1) 3> insert authors values("3", "Pink", "Cherry", "333 333-3333", "5 Ln.", "Vancouver", "BC", "33333", 1) 4> insert authors values("4", "Blue", "Albert", "444 444-4444", "7 Av.", "Vancouver", "BC", "44444", 1) 5> insert authors values("5", "Red", "Anne", "555 555-5555", "6 Av.", "Regina", "SK", "55555", 1) 6> insert authors values("6", "Black", "Michel", "666 666-6666", "3 Pl.", "Regina", "SK", "66666", 1) 7> insert authors values("7", "White", "Sylvia", "777 777-7777", "1 Pl.", "Rockville", "MD", "77777", 1) 8> insert authors values("8", "Yellow","Heather","888 888-8888", "3 Pu", "Vacaville", "CA", "88888", 0) 9> insert authors values("9", "Gold", "Dep", "999 999-9999", "5 Av.", "Oakland", "CA", "99999", 0) 10> insert authors values("10", "Siler", "Dean", "000 000-0000", "4 Av.", "Oakland", "CA", "00000", 1) 11> GO (1 rows affected) (1 rows affected) (1 rows affected) (1 rows affected) (1 rows affected) (1 rows affected) (1 rows affected) (1 rows affected) (1 rows affected) (1 rows affected) 1> 2> 3> CREATE PROC GetAuthors2 4> @colnum AS int 5> AS 6> DECLARE 7> @cmd AS varchar (8000) 8> SET @cmd = 9> "SELECT *" + CHAR (13) + CHAR(10) + 10> "FROM authors" + CHAR (13) + CHAR(10) + 11> "ORDER BY " + CAST (@colnum AS varchar (4)) 12> EXEC(@cmd) 13> GO 1> 2> drop proc GetAuthors2; 3> GO 1> 2> drop table authors; 3> GO 1></source>


Procedure with default parameter value

   <source lang="sql">

4> 5> 6> CREATE TABLE Employees 7> (orderid int NOT NULL, 8> customerid int NULL, 9> empname varchar(25) NOT NULL, 10> orderdate Datetime, 11> salary money NOT NULL); 12> GO 1> 2> CREATE PROC dbo.usp_EmployeeInsert 3> @orderid int, 4> @customerid char(5) = "ZZZZZ", 5> @orderdate datetime 6> AS 7> INSERT INTO Employees(orderid, customerid, orderdate) 8> VALUES(@orderid, @customerid, @orderdate) 9> GO 1> 2> 3> drop proc dbo.usp_EmployeeInsert; 4> GO 1> 2> drop table Employees; 3> GO</source>


Procedure with two parameters

   <source lang="sql">

4> CREATE TABLE employee( 5> id INTEGER NOT NULL PRIMARY KEY, 6> first_name VARCHAR(10), 7> last_name VARCHAR(10), 8> salary DECIMAL(10,2), 9> start_Date DATETIME, 10> region VARCHAR(10), 11> city VARCHAR(20), 12> managerid INTEGER 13> ); 14> GO 1> INSERT INTO employee VALUES (1, "Jason" , "Martin", 5890,"2005-03-22","North","Vancouver",3); 2> GO (1 rows affected) 1> INSERT INTO employee VALUES (2, "Alison", "Mathews",4789,"2003-07-21","South","Utown",4); 2> GO (1 rows affected) 1> INSERT INTO employee VALUES (3, "James" , "Smith", 6678,"2001-12-01","North","Paris",5); 2> GO (1 rows affected) 1> INSERT INTO employee VALUES (4, "Celia" , "Rice", 5567,"2006-03-03","South","London",6); 2> GO (1 rows affected) 1> INSERT INTO employee VALUES (5, "Robert", "Black", 4467,"2004-07-02","East","Newton",7); 2> GO (1 rows affected) 1> INSERT INTO employee VALUES (6, "Linda" , "Green" , 6456,"2002-05-19","East","Calgary",8); 2> GO (1 rows affected) 1> INSERT INTO employee VALUES (7, "David" , "Larry", 5345,"2008-03-18","West","New York",9); 2> GO (1 rows affected) 1> INSERT INTO employee VALUES (8, "James" , "Cat", 4234,"2007-07-17","West","Regina",9); 2> GO (1 rows affected) 1> INSERT INTO employee VALUES (9, "Joan" , "Act", 6123,"2001-04-16","North","Toronto",10); 2> GO (1 rows affected) 1> 2> select * from employee; 3> GO id first_name last_name salary start_Date region city managerid

---------- ---------- ------------ ----------------------- ---------- -------------------- ----------- 
         1 Jason      Martin          5890.00 2005-03-22 00:00:00.000 North      Vancouver                      3
         2 Alison     Mathews         4789.00 2003-07-21 00:00:00.000 South      Utown                          4
         3 James      Smith           6678.00 2001-12-01 00:00:00.000 North      Paris                          5
         4 Celia      Rice            5567.00 2006-03-03 00:00:00.000 South      London                         6
         5 Robert     Black           4467.00 2004-07-02 00:00:00.000 East       Newton                         7
         6 Linda      Green           6456.00 2002-05-19 00:00:00.000 East       Calgary                        8
         7 David      Larry           5345.00 2008-03-18 00:00:00.000 West       New York                       9
         8 James      Cat             4234.00 2007-07-17 00:00:00.000 West       Regina                         9
         9 Joan       Act             6123.00 2001-04-16 00:00:00.000 North      Toronto                       10

(9 rows affected) 1> 2> 3> CREATE PROCEDURE modify_empno (@old_no INTEGER, @new_no INTEGER) 4> AS UPDATE employee SET id = @new_no 5> WHERE id = @old_no 6> GO 1> 2> drop PROCEDURE modify_empno; 3> 4> drop table employee; 5> GO</source>


Select using value from parameter

   <source lang="sql">

4> 5> CREATE TABLE Authors( 6> au_id varchar(11) NOT NULL, 7> au_lname varchar(40) NOT NULL, 8> au_fname varchar(20) NOT NULL, 9> phone char(12) NOT NULL, 10> address varchar(40) NULL, 11> city varchar(20) NULL, 12> state char(2) NULL, 13> zip char(5) NULL, 14> contract bit NOT NULL 15> ); 16> GO 1> 2> CREATE PROC AuthorsInState 3> @state char(2) 4> AS 5> SELECT 6> * 7> FROM 8> authors 9> WHERE 10> state = @state 11> GO 1> 2> drop proc AuthorsInState; 3> GO 1> 2> drop table Authors; 3> GO 1> 2> 3> 4></source>


stored procedure can be executed with the parameter and assigned value

   <source lang="sql">

3> CREATE TABLE Product( 4> ProductID int NOT NULL, 5> Name nvarchar(25) NOT NULL, 6> ProductNumber nvarchar(25) , 7> Color nvarchar(15) NULL, 8> StandardCost money NOT NULL, 9> Size nvarchar(5) NULL, 10> Weight decimal(8, 2) NULL, 11> ProductLine nchar(20) NULL, 12> SellStartDate datetime NOT NULL, 13> SellEndDate datetime NULL 14> ) 15> GO 1> insert into Product values(1,"Product A", "1","Red",123.123,"1",1,"ProductLine A","1999-03-22","2000-03-22"); 2> GO (1 rows affected) 1> 2> 3> insert into Product values(2,"Product B", "2","Yellow",234.234,"1",3,"ProductLine B","2000-03-22","2001-03-22"); 4> GO (1 rows affected) 1> 2> 3> insert into Product values(3,"Product C", "3","Pink",345.345,"1",3,"ProductLine V","2001-09-22","2006-02-22"); 4> GO (1 rows affected) 1> 2> 3> insert into Product values(4,"Product D", "4","White",456.456,"1",4,"ProductLine D","2002-08-22","2006-03-22"); 4> GO (1 rows affected) 1> 2> 3> insert into Product values(5,"Product E", "5","Black",567.567,"1",5,"ProductLine E","2003-01-22","2003-04-22"); 4> GO (1 rows affected) 1> 2> 3> insert into Product values(6,"Product F", "6","Blue",678.678,"1",6,"ProductLine W","2004-02-22","2005-05-22"); 4> GO (1 rows affected) 1> 2> 3> insert into Product values(7,"Product G", "7","Drak",789.789,"1",7,"ProductLine Q","2005-03-22","2006-03-22"); 4> GO (1 rows affected) 1> 2> 3> insert into Product values(8,"Product H", "8","Gray",234.123,"1",8,"ProductLine F","2006-04-22","2006-09-22"); 4> GO (1 rows affected) 1> 2> 3> insert into Product values(9,"Product I", "9","Red",543.123,"1",9,"ProductLine R","2007-05-22","2008-03-22"); 4> GO (1 rows affected) 1> 2> 3> insert into Product values(0,"Product J", "0","Gold",765.123,"1",0,"ProductLine J","2008-06-22","2009-03-22"); 4> GO (1 rows affected) 1> 2> 3> 4> 5> Create PROCEDURE spProductCosts 6> @SubID Int 7> AS 8> SELECT ProductID, Name, ProductNumber, StandardCost FROM Product 9> WHERE ProductID = @SubID 10> GO 1> 2> 3> EXECUTE spProductCosts 1 4> GO ProductID Name ProductNumber StandardCost

------------------------- ------------------------- --------------------- 
         1 Product A                 1                                      123.1230

(1 rows affected) 1> 2> 3> EXECUTE spProductCosts @SubCategory = 1 4> GO HResult 0xC9, Level 16, State 4 Procedure or Function "spProductCosts" expects parameter "@SubID", which was not supplied. 1> 2> drop PROCEDURE spProductCosts; 3> GO 1> 2> 3> drop table Product; 4> GO</source>


Stored Procedure with Cursor Parameter

   <source lang="sql">

17> CREATE TABLE employee( 18> id INTEGER NOT NULL PRIMARY KEY, 19> first_name VARCHAR(10), 20> last_name VARCHAR(10), 21> salary DECIMAL(10,2), 22> start_Date DATETIME, 23> region VARCHAR(10), 24> city VARCHAR(20), 25> managerid INTEGER 26> ); 27> GO 1> INSERT INTO employee VALUES (1, "Jason" , "Martin", 5890,"2005-03-22","North","Vancouver",3); 2> GO (1 rows affected) 1> INSERT INTO employee VALUES (2, "Alison", "Mathews",4789,"2003-07-21","South","Utown",4); 2> GO (1 rows affected) 1> INSERT INTO employee VALUES (3, "James" , "Smith", 6678,"2001-12-01","North","Paris",5); 2> GO (1 rows affected) 1> INSERT INTO employee VALUES (4, "Celia" , "Rice", 5567,"2006-03-03","South","London",6); 2> GO (1 rows affected) 1> INSERT INTO employee VALUES (5, "Robert", "Black", 4467,"2004-07-02","East","Newton",7); 2> GO (1 rows affected) 1> INSERT INTO employee VALUES (6, "Linda" , "Green" , 6456,"2002-05-19","East","Calgary",8); 2> GO (1 rows affected) 1> INSERT INTO employee VALUES (7, "David" , "Larry", 5345,"2008-03-18","West","New York",9); 2> GO (1 rows affected) 1> INSERT INTO employee VALUES (8, "James" , "Cat", 4234,"2007-07-17","West","Regina",9); 2> GO (1 rows affected) 1> INSERT INTO employee VALUES (9, "Joan" , "Act", 6123,"2001-04-16","North","Toronto",10); 2> GO (1 rows affected) 1> 2> select * from employee; 3> GO id first_name last_name salary start_Date region city managerid

---------- ---------- ------------ ----------------------- ---------- -------------------- ----------- 
         1 Jason      Martin          5890.00 2005-03-22 00:00:00.000 North      Vancouver                      3
         2 Alison     Mathews         4789.00 2003-07-21 00:00:00.000 South      Utown                          4
         3 James      Smith           6678.00 2001-12-01 00:00:00.000 North      Paris                          5
         4 Celia      Rice            5567.00 2006-03-03 00:00:00.000 South      London                         6
         5 Robert     Black           4467.00 2004-07-02 00:00:00.000 East       Newton                         7
         6 Linda      Green           6456.00 2002-05-19 00:00:00.000 East       Calgary                        8
         7 David      Larry           5345.00 2008-03-18 00:00:00.000 West       New York                       9
         8 James      Cat             4234.00 2007-07-17 00:00:00.000 West       Regina                         9
         9 Joan       Act             6123.00 2001-04-16 00:00:00.000 North      Toronto                       10

(9 rows affected) 1> 2> 3> 4> CREATE PROC Contacts(@crsContacts CURSOR VARYING OUTPUT) 5> AS 6> SET 7> @crsContacts = CURSOR FAST_FORWARD 8> FOR 9> SELECT first_Name FROM Employee 10> OPEN @crsContacts 11> GO 1> 2> --Calling a Stored Procedure with a Cursor Parameter 3> DECLARE 4> @crs CURSOR 5> EXEC Contacts @crs output 6> 7> FETCH @crs 8> 9> 10> 11> drop table employee; 12> GO first_Name

Jason (1 rows affected) 1></source>


The syntax for declaring parameters

   <source lang="sql">

@parameter_name_1 data_type [= default] [OUTPUT] [, @parameter_name_2 data_type [= default] [OUTPUT]]... Typical parameter declarations @DateVar smalldatetime -- Input parameter that accepts 

                                -- a date/time value

@BankerVar varchar(40) = NULL -- Optional input parameter that accepts 

                                -- a character value

@InvTotal money OUTPUT -- Output parameter that returns 

                                -- a monetary value</source>

Wildcards in Parameters

   <source lang="sql">

5> 6> CREATE TABLE titles( 7> title_id varchar(20), 8> title varchar(80) NOT NULL, 9> type char(12) NOT NULL, 10> pub_id char(4) NULL, 11> price money NULL, 12> advance money NULL, 13> royalty int NULL, 14> ytd_sales int NULL, 15> notes varchar(200) NULL, 16> pubdate datetime NOT NULL 17> ) 18> GO 1> 2> insert titles values ("1", "Secrets", "popular_comp", "1389", $20.00, $8000.00, 10, 4095,"Note 1","06/12/94") 3> insert titles values ("2", "The", "business", "1389", $19.99, $5000.00, 10, 4095,"Note 2","06/12/91") 4> insert titles values ("3", "Emotional", "psychology", "0736", $7.99, $4000.00, 10, 3336,"Note 3","06/12/91") 5> insert titles values ("4", "Prolonged", "psychology", "0736", $19.99, $2000.00, 10, 4072,"Note 4","06/12/91") 6> insert titles values ("5", "With", "business", "1389", $11.95, $5000.00, 10, 3876,"Note 5","06/09/91") 7> insert titles values ("6", "Valley", "mod_cook", "0877", $19.99, $0.00, 12, 2032,"Note 6","06/09/91") 8> insert titles values ("7", "Any?", "trad_cook", "0877", $14.99, $8000.00, 10, 4095,"Note 7","06/12/91") 9> insert titles values ("8", "Fifty", "trad_cook", "0877", $11.95, $4000.00, 14, 1509,"Note 8","06/12/91") 10> GO (1 rows affected) (1 rows affected) (1 rows affected) (1 rows affected) (1 rows affected) (1 rows affected) (1 rows affected) (1 rows affected) 1> 2> CREATE PROC gettitles 3> @tid varchar(6) = "%" 4> 5> 6> 7> AS 8> 9> SELECT title 10> FROM titles 11> WHERE title_id LIKE @tid 12> 13> IF @@rowcount = 0 14> PRINT "There are no titles matching your input" 15> RETURN 16> GO 1> 2> EXEC gettitles bu1032 3> GO title

(0 rows affected) There are no titles matching your input 1> 2> EXEC gettitles "bu%" 3> GO title

(0 rows affected) There are no titles matching your input 1> 2> 3> drop table titles; 4> GO 1> 2> drop PROC gettitles ; 3> GO 1></source>

Источник — «http://sqle.ru/index.php?title=SQL_Server/T-SQL_Tutorial/Procedure_Function/Parameter&oldid=7058»