Freetutes.com

VB6 beginners tutorial - Learn VB6

Advanced VB6 tutorial - Learn Advanced VB6

Systems Analysis - System analysis and Design tutorial for Software Engineering


You are here: Visual Basic > Advanced VB6 tutorial > Chapter 9

Creating Stored Procedures

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.


  

<< Previous | Content | Next >>

Home | About Us | Privacy Policy | Contact Us

Copyright © Freetutes.com | All Rights Reserved