SQL Server/T-SQL Tutorial/Procedure Function/Parameter — различия между версиями
Admin (обсуждение | вклад) м (1 версия) |
|
(нет различий)
| |
Текущая версия на 10:25, 26 мая 2010
Содержание
- 1 Call procedure with parameter name
- 2 Check parameter value with if statement
- 3 Check value range for input parameter
- 4 Code that omits both optional parameters
- 5 Code that omits one optional parameter
- 6 Code that passes the parameters by name
- 7 Code that passes the parameters by position
- 8 Parameterization: @parameter_name [AS] datatype [= default|NULL] [VARYING] [OUTPUT|OUT]
- 9 parameters can be passed explicitly by value
- 10 Parameter with null default value
- 11 Pass column as the parameter
- 12 Passing the ORDER BY Column as a Parameter, Using a Column Number
- 13 Passing the ORDER BY Column as a Parameter, Using Dynamic Execution
- 14 Procedure with default parameter value
- 15 Procedure with two parameters
- 16 Select using value from parameter
- 17 stored procedure can be executed with the parameter and assigned value
- 18 Stored Procedure with Cursor Parameter
- 19 The syntax for declaring parameters
- 20 Wildcards in Parameters
Call procedure with parameter name
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
Check parameter value with if statement
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
Check value range for input parameter
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
Code that omits both optional parameters
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>
Code that omits one optional parameter
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>
Code that passes the parameters by name
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>
Code that passes the parameters by position
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>
Parameterization: @parameter_name [AS] datatype [= default|NULL] [VARYING] [OUTPUT|OUT]
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
parameters can be passed explicitly by value
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
Parameter with null default value
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
Pass column as the parameter
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>
Passing the ORDER BY Column as a Parameter, Using a Column Number
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>
Passing the ORDER BY Column as a Parameter, Using Dynamic Execution
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>
Procedure with default parameter value
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
Procedure with two parameters
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
Select using value from parameter
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>
stored procedure can be executed with the parameter and assigned value
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
Stored Procedure with Cursor Parameter
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>
The syntax for declaring parameters
@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
Wildcards in Parameters
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>