SQL Server/T-SQL/Store Procedure Function/Procedure Call
Call the stored procedure
11> create table Billings (
12> BankerID INTEGER,
13> BillingNumber INTEGER,
14> BillingDate datetime,
15> BillingTotal INTEGER,
16> TermsID INTEGER,
17> BillingDueDate datetime ,
18> PaymentTotal INTEGER,
19> CreditTotal INTEGER
20>
21> );
22> 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 PROC spInsertBilling
4> @BankerID int, @BillingNumber varchar(50),
5> @BillingDate smalldatetime, @BillingTotal money,
6> @TermsID int, @BillingDueDate smalldatetime
7> AS
8> DECLARE @ErrorVar int
9> INSERT Billings (BankerID)
10> VALUES (@BankerID)
11> SET @ErrorVar = @@ERROR
12> IF @ErrorVar <> 0
13> BEGIN
14> IF @ErrorVar = 547
15> PRINT "Not a valid BankerID!"
16> ELSE
17> PRINT "An unknown error occurred."
18> RETURN @ErrorVar
19> END
20> GO
1>
2> DECLARE @ReturnVar int
3> EXEC @ReturnVar = spInsertBilling
4> 799,"ZXK-799","2002-07-01",299.95,1,"2001-08-01"
5> PRINT "Return code was: " + CONVERT(varchar,@ReturnVar)
6> GO
(1 rows affected)
(1 rows affected)
Return code was: 0
1>
2>
3> drop PROC spInsertBilling;
4> GO
1>
2>
3> drop table Billings;
4> GO
1>
the parameters can be passed explicitly by value
2>
3>
4> CREATE TABLE Product(
5> ProductID int NOT NULL,
6> Name nvarchar(25) NOT NULL,
7> ProductNumber nvarchar(25) ,
8> Color nvarchar(15) NULL,
9> StandardCost money NOT NULL,
10> Size nvarchar(5) NULL,
11> Weight decimal(8, 2) NULL,
12> ProductLine nchar(20) NULL,
13> SellStartDate datetime NOT NULL,
14> SellEndDate datetime NULL
15> )
16> 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> insert into Product values(2,"Product B", "2","Yellow",234.234,"1",3,"ProductLine B","2000-03-22","2001-03-22");
2> GO
(1 rows affected)
1> insert into Product values(3,"Product C", "3","Pink",345.345,"1",3,"ProductLine V","2001-09-22","2006-02-22");
2> GO
(1 rows affected)
1> insert into Product values(4,"Product D", "4","White",456.456,"1",4,"ProductLine D","2002-08-22","2006-03-22");
2> GO
(1 rows affected)
1> insert into Product values(5,"Product E", "5","Black",567.567,"1",5,"ProductLine E","2003-01-22","2003-04-22");
2> GO
(1 rows affected)
1> insert into Product values(6,"Product F", "6","Blue",678.678,"1",6,"ProductLine W","2004-02-22","2005-05-22");
2> GO
(1 rows affected)
1> insert into Product values(7,"Product G", "7","Drak",789.789,"1",7,"ProductLine Q","2005-03-22","2006-03-22");
2> GO
(1 rows affected)
1> insert into Product values(8,"Product H", "8","Gray",234.123,"1",8,"ProductLine F","2006-04-22","2006-09-22");
2> GO
(1 rows affected)
1> insert into Product values(9,"Product I", "9","Red",543.123,"1",9,"ProductLine R","2007-05-22","2008-03-22");
2> GO
(1 rows affected)
1> insert into Product values(0,"Product J", "0","Gold",765.123,"1",0,"ProductLine J","2008-06-22","2009-03-22");
2> GO
(1 rows affected)
1>
2>
3> CREATE PROCEDURE spEmployeeByCost
4> @SubID Int, @Cost Money
5> AS
6> SELECT ProductID, Name, ProductNumber, StandardCost
7> FROM Product
8> WHERE ProductID = @SubID
9> AND StandardCost > @Cost
10> GO
1> EXECUTE spEmployeeByCost 1, $1000.00
2> GO
ProductID Name ProductNumber StandardCost
----------- ------------------------- ------------------------- ---------------------
(0 rows affected)
1>
2> --Or the parameters can be passed explicitly by value.
3> EXECUTE spEmployeeByCost @Cost = $1000.00, @SubID = 1
4> GO
ProductID Name ProductNumber StandardCost
----------- ------------------------- ------------------------- ---------------------
(0 rows affected)
1> drop table Product;
2> GO
Use procedure as a parameteried view
2>
3>
4> CREATE TABLE Product(
5> ProductID int NOT NULL,
6> Name nvarchar(25) NOT NULL,
7> ProductNumber nvarchar(25) ,
8> Color nvarchar(15) NULL,
9> StandardCost money NOT NULL,
10> Size nvarchar(5) NULL,
11> Weight decimal(8, 2) NULL,
12> ProductLine nchar(20) NULL,
13> SellStartDate datetime NOT NULL,
14> SellEndDate datetime NULL
15> )
16> 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> insert into Product values(2,"Product B", "2","Yellow",234.234,"1",3,"ProductLine B","2000-03-22","2001-03-22");
2> GO
(1 rows affected)
1> insert into Product values(3,"Product C", "3","Pink",345.345,"1",3,"ProductLine V","2001-09-22","2006-02-22");
2> GO
(1 rows affected)
1> insert into Product values(4,"Product D", "4","White",456.456,"1",4,"ProductLine D","2002-08-22","2006-03-22");
2> GO
(1 rows affected)
1> insert into Product values(5,"Product E", "5","Black",567.567,"1",5,"ProductLine E","2003-01-22","2003-04-22");
2> GO
(1 rows affected)
1> insert into Product values(6,"Product F", "6","Blue",678.678,"1",6,"ProductLine W","2004-02-22","2005-05-22");
2> GO
(1 rows affected)
1> insert into Product values(7,"Product G", "7","Drak",789.789,"1",7,"ProductLine Q","2005-03-22","2006-03-22");
2> GO
(1 rows affected)
1> insert into Product values(8,"Product H", "8","Gray",234.123,"1",8,"ProductLine F","2006-04-22","2006-09-22");
2> GO
(1 rows affected)
1> insert into Product values(9,"Product I", "9","Red",543.123,"1",9,"ProductLine R","2007-05-22","2008-03-22");
2> GO
(1 rows affected)
1> insert into Product values(0,"Product J", "0","Gold",765.123,"1",0,"ProductLine J","2008-06-22","2009-03-22");
2> GO
(1 rows affected)
1>
2> Create PROCEDURE spProductCosts
3> @SubID Int
4> As
5> SELECT ProductID, Name, ProductNumber, StandardCost
6> FROM Product
7> WHERE ProductID = @SubID
8> GO
1>
2> drop PROCEDURE spProductCosts;
3> GO
1>
2> drop table Product;
3> GO