Stored procedures provide an alternative to executing batches of SQL statements in ADO. We looked briefly at how we can call a stored procedure earlier. Let's take a look at how to get values back out of a stored procedure. Here's a simple code sample showing how to call a stored procedure with a single output parameter.
Set cmd = Server.CreateObject("ADODB.Command")
oCmd.ActiveConnection = "dsn=SQLForum;database=pubs;uid=sa;pwd=;"
oCmd.CommandText = "{call recordcount(?)}"
'now specify parameter info
oCmd.Parameters.Append oCmd.CreateParameter("cnt", adInteger, adParamOutput)
oCmd.Execute
Response.Write "RecordCount = " & oCmd(0)
In this example, we have a stored procedure called recordcount, which accepts one integer parameter and returns the number of records in our table. Alternately we can use this approach with the same stored procedure:
Set oCon = Server.CreateObject("ADODB.Connection")
Set oCmd = Server.CreateObject("ADODB.Command")
oCon.Open "SQLForum", "sa", ""
Set oCmd.ActiveConnection = oCon
oCmd.CommandText = "{? = call recordcount}"
'now specify parameter info
oCmd(0).Direction = adParamReturnValue
oCmd.Execute
Response.Write "RecordCount = " & oCmd(0)