Calling stored procedures From ASP pages

For demonstration purposes only! Please be aware of SQL Injection, and techniques to avoid.

SELECT * FROM examples WHERE new = 1;

Remember this? The original sql statement. It has now been rendered obsolete but we need to tell the database to use the procedure instead of the old sql statement. Assume we expanded on the original sql statement and the rest of the asp document looked like this:

<% 
Option Explicit 
Response.Buffer = True
 
dim c, r, sql
 
' This Is The OLD SQL statement to be replaced 
sql = "SELECT * FROM examples WHERE new = 1;"
 
set c = server.createobject("adodb.connection")
 c.Open Application("dbConn")
 set r = c.Execute(sql)
 do while not r.bof and not r.eof
     response.write r("exampleName") & "<BR>"
     r.movenext
 loop 
r.close
 c.Close
 set r = Nothing 
set c = Nothing 
%>

Replacing the old statement is real easy. You know the name of the stored procedure: sp_GetNewExamples so all you need to do is put the sql server/access keyword EXECUTE before the stored procedure's name:

EXECUTE sp_GetNewExamples
If we zoom out, the new sql string looks like this:

<% 
Option Explicit 
Response.Buffer = True
 
dim c, r, sql
 
' This Is The NEW SQL statement that calls 
' a Stored Procedure ..... 
sql = "EXECUTE sp_GetNewExamples"
 
set c = server.createobject("adodb.connection")
 c.Open Application("dbConn")
 set r = c.Execute(sql)
 do while not r.bof and not r.eof
     response.write r("exampleName") & "<BR>"
     r.movenext
 loop 
r.close
 c.Close
 set r = Nothing 
set c = Nothing 
%>

asp SQL Stored Procedures SELECT * SQL Statement Recordset ADODB