ASP and SQL Server store procedures

Using SQL Server Stored Procedures with ASP ....

SQL Server Stored Procedures give much more functionality to ASP coding enabling you incorporate multiple SQL statements into one procedure. Being part of the server, the procedures execute faster and can make your front end ASP code more modular and easier to maintain.

The example shown here illustrates passing a parameter value into a stored procedure and returning a value from a stored parameter. The example can only be run using SQL Server or MSDE - an MS Access database cannot be used.

The stored procedure accepts a course code as the input parameter and then returns the number an value which is the number of students registered on that course.

Installing MSDE

If you do not have SQL Server, you can still use MSDE which is a kind of a half-way house between SQL Server and MS Access. It is located on the Office 2000 distribution CD and you install it from the
\SQL\X86\SETUP folder and running SETUPSQL.EXE.

MSDE does not have the management facilities that SQL Server has, but you do get a full database system where you can create stored procedures and try out other features which are available on SQL Server.

After you have installed MSDE, a directory will be created at c:\msql\data which is where the database .mdf files will be located.

To use MS Access 2000 as the front end, start up Access, and at the first dialog box where it asks 'Create a new database using' select 'Access database wizards, pages and reports' click on OK. In the 'General' tab click on 'Project (Existing database)' to connect to an existing database or 'Project (New database)' to start a new database.

In the case of a new project, you need to enter a .adp (Access Data Project) filename and then the 'Microsoft SQL Server Database Wizard' will start up. You then enter a name for the database. This will then become the .mdf file which will be located in the c:\msql\data folder.

From then on you only need to load up the .apd file in MS Access which becomes the front end to the database.

Using the download .mdf file

The code for this example which may be downloaded includes an .mdf file which should be copied into the c:\msql\data folder. Make sure that SQL is running using the SQL Server Service Manager.

The Stored Procedure

CREATE PROCEDURE qryNumberOnCourse
@CourseID nvarchar(10), /* Input parameter */
@Number int OUTPUT /* Output parameter */
     AS
SELECT @Number=count(tblStudents.StudentID)
     FROM tblStudents INNER JOIN
     tblEnrolment ON
     tblStudents.StudentID = tblEnrolment.StudentID
WHERE tblEnrolment.CourseID = @CourseID

The purpose of this stored procedure is to return the number of students who are enrolled on a particular course code. So the input parameter is the course code and the output parameter is the number of students.

The ASP Code

<%@ Language=VBScript %>
<%Option explicit

Dim cmd, rs, connect, intNumber
Const adCmdStoredProc = &H0004
Const adParamInput = &H0001
Const adParamOutput = &H0002
Const adVarChar = 200
Const adInteger = 3

Set cmd = Server.CreateObject ("ADODB.Command")
connect = "Provider=SQLOLEDB;Persist Security Info=False;User ID=sa;" &_
      "Initial Catalog=coursesSQL;Data Source=yourservername"
cmd.ActiveConnection = connect
cmd.CommandText = "qryNumberOnCourse"
cmd.CommandType = adCmdStoredProc
cmd.Parameters.Append cmd.CreateParameter &_
      ("@CourseID",adVarChar,adParamInput ,10,"C001")
cmd.Parameters.Append cmd.CreateParameter &_
      ("@Number",adInteger,adParamOutput)
Set rs = cmd.Execute

intNumber = comm.Parameters("@Number")
set cmd = nothing
%>

Set cmd = Server.CreateObject ("ADODB.Command")

This starts with creating a command object which provides us with the ability to execute our query.

connect = "Provider=SQLOLEDB;Persist Security Info=False;User ID=sa;" &_
"Initial Catalog=coursesSQL;Data Source=yourservername"

The connection string requires you to enter in your server name.

cmd.ActiveConnection = connect

The active connection defines the connection to our database.

cmd.CommandText = "qryNumberOnCourse"

CommandText defines which command we are about to execute, which is our query.

cmd.CommandType = adCmdStoredProc

CommandType identifies the type of command being executed, which in this case is a stored procedure.

cmd.Parameters.Append cmd.CreateParameter &_
("@CourseID",adVarChar,adParamInput ,10,"C001")

We must create a parameter object with the values we are using. In this example we are hard coding the value to be entered into the query which is C001.

cmd.Parameters.Append cmd.CreateParameter &_
("@Number",adInteger,adParamOutput)

The output parameter is going to be passed to the variable @Number.

Set rs = cmd.Execute
intNumber = cmd.Parameters ("@Number")

Now we execute our parameter query and the result is placed into intNumber

set cmd = nothing
Response.Write (intNumber)

Finally, close and de-reference the objects and then display the variable intNumber.

The source code and the MSDE database for this example may be downloaded by clicking here.