How to execute Stored Procedures from ASP

Tweet

The power of Stored Procedures in SQL Server is quite useful when developing web applications. It is sure that using such power in your code is very important in order to be sure the queries are executed quickly and efficiently. But, how do you execute those queries from asp? Actually it is very easy as soon as you understand the logic behind it. I've seen many web site and blogs explaining that, however it seems that a simple and straight way of showing the method is missing. Here we go.
First of all, define the database connection:

<%
Dim CONN_STRING
CONN_STRING = "Driver={SQL Server};Server=serverpath;Database=dbname;Uid=userid;Pwd=password;"
%>

Now, use the ADODB.Command to retrieve the recordset:

<%
set sp_1 = Server.CreateObject("ADODB.Command")
sp_1.ActiveConnection = CONN_STRING
sp_1.CommandText = "dbo.storedprocedurename"
sp_1.CommandType = 4
sp_1.CommandTimeout = 0
sp_1.Prepared = true
sp_1.Parameters.Append sp_1.CreateParameter("@RETURN_VALUE", 3, 4)
sp_1.Parameters.Append sp_1.CreateParameter(name,type,direction,size,value)
set returned_value = sp_1.Execute
returned_value_numRows = 0
%>

And that's all. Isn't that easy?
The .CreateParameter methods use some parameters like name, type, direction, size and value. Those parameters are actually optional, but if you need to pass values to your stored procedure, you should understand how and why those parameters are used. The w3school might help you in this matter.
In general, the command object has interesting properties and methods which you might find useful to know.