SQL Server/T-SQL/Store Procedure Function/Procedure Call
Call the stored procedure
<source lang="sql">
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>
</source>
the parameters can be passed explicitly by value
<source lang="sql">
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
</source>
Use procedure as a parameteried view
<source lang="sql">
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
</source>
