You can create a stored procedure in SQL Server with a special CREATE PROCEDURE
query. The CREATE PROCEDURE query uses the following format:
CREATE PROCEDURE ProcName As ProcText
where ProcName is the name that you will give to the stored procedure and ProcText
is one or more lines of SQL syntax.
Listing 9.4 gives an example of the creation of a simple stored procedure
that contains a Select statement to retrieve records from a single table.
LISTING 9.4
SQL SERVER QUERY TO CREATE A SIMPLE STORED PROCEDURE
CREATE PROCEDURE Employee_All As
Select * from Employee
A stored procedure is usually more complicated than the simple example of
Listing 9.4. In SQL Server, for example, a stored procedure can use flow-of-control keywords (such
as if and while), support local memory variables, receive and modify parameters, use temporary
tables, and return a value to the requester as if the stored procedure were a function.
The syntax of a SQL Server stored procedure's execution language should
look very familiar to a Visual Basic programmer, although it is not exactly the same. Listing
9.5 gives an example of a more complex stored procedure. Note the use of local variables,
parameters, a return value, and nonexecutable comment lines. It is also possible to use flow-of-control
constructs such as if and while.
LISTING 9.5
SQL SERVER QUERY TO CREATE A COMPLEX STORED PROCEDURE
—local variables
declare @chTaxRegNbr char(10)
declare @dtToday datetime
declare @MaxDPD int
—Get TaxRegNbr for this customer
select @chTaxRegNbr = taxregnbr
from customer
where custid = @parmCustID
—Initialize variable for Max DPD &
current date
select @MaxDPD = 0
select @dtToday = GetDate()
—Open table that holds CUSTIDS from CUSTOMER
—for records with same TAXREGNBR
select custid into #tempCustID_TaxReg from customer
where taxregnbr = @chtaxregNbr
order by CustID
—get max DPD of any invoice
select @MaxDPD = MAX(datediff(day,duedate,@dtToday))
from ardoc
where custid in (select custid from
#tempCustID_TaxReg)
AND DocType IN ('IN','DM','FI')
AND Rlsed = 1
AND OpenDoc = 1
and duedate > 'JAN 01, 1900'
—destroy the temporary table
drop table #tempcustid_taxreg
—Make sure intermediate result is 0 if
no records found
select @MaxDPD = ISNull(@MaxDPD,0)
—return final value
return @MaxDPD
GO
Of particular interest for the following discussion are the parameters and
the return value of a stored procedure. The parameters in a SQL Server stored procedure
are declared immediately after the procedure name in the CREATE PROCEDURE query and before
the As keyword.
On the other hand, the return value of a stored procedure is passed back to
the requester by placing the keyword return in front of the value to be returned.
Refer again to Listing 9.5 for examples of parameters and a return statement.
Note that parameters are declared using C-style syntax, as are local variables:
@parm_name datatype
where datatype is an appropriate SQL Server data type. Parameters can be used
to pass information back to the requester. These parameters are called output parameters and
are designated in the parameter's declaration with the keyword output after the datatype.
Multiple parameter declarations are separated by commas, as illustrated in
the listing.
