Calling stored procedures From ASP pages

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:

<%  @  Language = vbscript %>
<%
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:

<%  @  Language = vbscript %>
<%
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
%>

My3C's
perrychicker

Back | Tell A Friend | Search this Site
© 1998 - 2010 psacake.com
Version 3.23

Send me One Million FREE Guaranteed Visitors