Executing Store Procedure (SP) in ASP

A stored procedure is a group of Transact-SQL statements compiled into a single execution plan.

In a layman language a stored procedure is an already written SQL statement that is saved in the database. If we find ourselves using the same query over and over again, it would make sense to put it into a stored procedure. When we put this SQL statement in a stored procedure, we can then run the stored procedure from the database's command environment , using the exec command.

An example is:

CREATE PROCEDURE NameOfStoreProcedure

AS

SELECT FirstName, LastName FROM TableName;

GO

EXEC NameOfStoreProcedure

The name of the stored procedure is "NameOfStoreProcedure ", and "exec" tells SQL Server to execute the code in the stored procedure.

There are two main advantages of using store procedure in our program

• Because stored procedures are stored within the DBMS, bandwidth and execution time are reduced. This is because a single stored procedure can execute a complex set of SQL statements.
• With Store procedure we will store our entire business login inside the database itself. So if we plan to migrate from one technology to another say from classic ASP to .NET then we only need to change the user interface reset of the business logic remain same.

Call a stored procedure is through the connection object

The easiest way to call a stored procedure is through the connection object. This can be as simple as four lines of code: 

Dim ObjConn

Set ObjConn = Server.CreateObject("ADODB.Connection")

ObjConn.Open Application("Connection_String")

'Call the stored procedure to increment a counter on the page

ObjConn.Execute "exec NameOfStoreProcedure"

This case assumes there are no parameters, no results, and no error handling. Most of the time, we'll need more than this.

Call a stored procedure using Command Object

One of the major advantages of a Command object over connection object is the ability to use parameters. That means with command object Output parameters of Store procedures are retrieval while with connection object it’s not possible.

Any time we need to get data back from a stored procedure via a parameter, we need to invoke it using the ADO Command object. The sample below demonstrates the minimal properties and methods required to invoke a stored procedure call and return a value in an output parameter. 

Dim objConn

Dim objCmd

'Instantiate objects

Set objConn = Server.CreateObject("ADODB.Connection")

set objCmd = Server.CreateObject("ADODB.Command")

conn.Open Application("ConnectionString")

With objCmd

.ActiveConnection = conn 'We can also just specify a connection string here

.CommandText = "sp_InsertArticle"

.CommandType = adCmdStoredProc 'Requires the adovbs.inc file or typelib meta tag

'Add Input Parameters

.Parameters.Append .CreateParameter("@columnist_id", adDouble, adParamInput, , columnist_id)

.Parameters.Append .CreateParameter("@url", adVarChar, adParamInput, 255, url)

.Parameters.Append .CreateParameter("@title", adVarChar, adParamInput, 99, url)

.Parameters.Append .CreateParameter("@description", adLongVarChar, _

adParamInput, 2147483647, description)

'Add Output Parameters

.Parameters.Append .CreateParameter("@link_id", adInteger, adParamOutput, , 0)

'Execute the function

'If not returning a recordset, use the adExecuteNoRecords parameter option

.Execute, , adExecuteNoRecords

link_id = .Parameters("@link_id")

End With