SQL Stored Procedures (in ASP)

SPs are an advanced feature in SQL server that offers you to create, compile and run SQL statements in the server itself, to isolate your business logic from data logic and to improve the performance of your application. In short, write SQL queries in a specific format in the SQL server and call them from your application, instead of writing queries inside your program code.

Where to write Stored Procedures?

You can write Stored Procedures in the following possible ways:

Using SQL Server Enterprise manager - To create a stored procedure, Open SQL server enterprise manager,
- Expand a server group; click and expand a server.
- Expand Databases; select the database where under which you want to create the procedure.
- Right-click Stored Procedures; then click New Stored Procedure..
- Type the stored procedure. Press CTRL-TAB to indent the text of a stored procedure.
- To check the syntax, click Check Syntax.
- To set the permissions, click Permissions.
Using Query Analyzer - Open query analyzer, select the DB, type the procedure and execute it.
From ASP Code - Write the procedures, create the connection with the DB using ADO, call the procedures directly from your code.
Using Visual Studio.NET - VS.NET users can write their procedures using "create New Procedure" like in Enterprise Manager and check the syntax there itself.
Create using Enterprise manager wizard - We can also use the wizard to create the procedures. Follow the steps to use the wizard. Expand a server group; then expand the server in which to create the view. On the Tools menu click Wizards... Expand Database. Double-click Create Stored Procedure Wizard. Complete the steps in the wizard.

Stored Procedure example:

CREATE PROCEDURE sp_councillors AS
BEGIN
SELECT au_fname,au_lname,au_party,au_elected
FROM tblCouncillors
WHERE au_county = 'CUMBRIA'
END
GO

The above code would be entered in SQL Server Enterprise Manager

Input parameters

To pass parameters to the stored procedure, prefix the field name with a @ symbol:

CREATE PROCEDURE sp_councillors
@political_party varchar(50)
AS
BEGIN
SELECT au_fname,au_lname,au_party,au_elected
FROM tblCouncillors
WHERE au_party = @political_party
END
GO

In the above example, @political_party is the input parameter and the values will be sent by your ASP or VB code.

Output parameters

CREATE PROCEDURE sp_total_councillors
@political_party varchar(50),
@count_councillors int OUTPUT
AS
BEGIN
SELECT @count_councillors = Count(*)
FROM tblCouncillors
WHERE au_party = @political_party
END
GO

Here the total no of councillors who are in the specified political party will be counted and sent through the output parameter. You can see the keyword OUTPUT to differentiate the output parameter from the input parameter.

Calling Stored Procedures from ASP code

The following code snippet would be typed into an ASP page:

<%
!--#INCLUDE VIRTUAL="/include/adovbs.inc"-->
Dim objConn, objCmd, objParam
set objConn = Server.CreateObject("ADODB.Connection")
objConn.Open "DSN=testdb;uid=test;pwd=test"
Set objCmd = Server.CreateObject("ADODB.Command")
objCmd.CommandText = "sp_total_councillors"
objCmd.CommandType = adCmdStoredProc
objcmd.ActiveConnection = objConn
objCmd.Parameters.Append objCmd.CreateParameter ("@political_party",adVarChar,adParamInput,50,"LABOUR")
objCmd.Parameters.Append objCmd.CreateParameter ("@count_councillors",adInteger,adParamOutput,4)
ObjCmd.Execute
%>
<HTML>
<BODY>
No of Councillors who are members of the LABOUR Party: <%= objCmd.Parameters.("@count_councillors")%>
</BODY>
</HTML>

In the above example we are specifying an Input AND an Output parameter. Note the input parameter has an extra value - "LABOUR", which is used to pass the selection data to SQL.