Execute stored proc having input and output params, returned recordset and return value

Return Values how-to Execute a Stored Proc's

This demo It's called ReturnValue.asp and shows you how to execute a stored procedure that has input params, output params, a returned recordset and a return value.

<!-- Author: John Bailey -->

<%@ Language=VBScript %>

<%
'CODE TO CREATE THE STORED PROCEDURE THAT THIS ASP ACCESSES
'Just remove all comments after this line, paste into the SQL query analyzer and run.

'-- insures you use the right database
'use pubs
'GO
'
'-- Creates the procedure
'create procedure sp_PubsTest
'
'-- declare three parameter variables
'  @au_lname varchar (20), 
'  @intID int,
'  @intIDOut int OUTPUT
'
'AS
'
'SELECT @intIDOut = @intID + 1
'
'SELECT *
'FROM authors
'WHERE au_lname LIKE @au_lname + '%'

'RETURN @intID + 2

%>



<%

'THIS IS THE ASP CODE. Just run from the server.

Option Explicit

Dim CmdSP
Dim adoRS
Dim adCmdSPStoredProc
Dim adParamReturnValue
Dim adParaminput
Dim adParamOutput
Dim adInteger
Dim iVal
Dim oVal
Dim adoField
Dim adVarChar

adCmdSPStoredProc = 4
adParamReturnValue = 4
adParaminput = 1
adParamOutput = 2
adInteger = 3
adVarChar = 200

iVal = 5
oVal = 3


  '-- Create a command object --
  set CmdSP = Server.CreateObject("ADODB.Command")

  '-- Make an ODBC connection to the (local) SQL server,
  '-- connecting to the Pubs database with the default sa login and empty password
  CmdSP.ActiveConnection = "Driver={SQL Server};server=(local);Uid=sa;Pwd=;Database=Pubs"
 

  '-- define the name of the command 
  CmdSP.CommandText = "sp_PubsTest"
 
 
  '-- define the type of the command as a stored procedure (numeric value = 4)
  CmdSP.CommandType = adCmdSPStoredProc
 
 
  '-- define the first parameter - the one the procedure will return
  '-- the calls are:
  '--   CmdSP.Parameters.Append: append this parameter to the collection for this command object
  '--   CmdSP.CreateParameter(): creates the parameter using the values given:
  '--      "RETURN_VALUE" is the name of the parameter for later reference
  '--      adInteger (value = 3) indicates this parameter is an integer datatype
  '--      adParamReturnValue (value = 4) indicates this parameter is expected to be returned
  '--      4 is an arbitrary initial value for this parameter

  CmdSP.Parameters.Append CmdSP.CreateParameter("RETURN_VALUE", adInteger, adParamReturnValue, 4)


  '-- define the first parameter - the one the procedure will return
  '-- the calls are:
  '--   CmdSP.Parameters.Append: append this parameter to the collection for this command object
  '--   CmdSP.CreateParameter(): creates the parameter using the values given:
  '--      "@au_lname" is the name of the parameter for later reference
  '--      adVarChar (value = 200) indicates this parameter is a string datatype
  '--      adParamInput (value = 1) indicates this parameter is for input
  '--      20 is the size of the string in characters
  '--      "M" is an arbitrary initial value for this parameter
 
  CmdSP.Parameters.Append CmdSP.CreateParameter("@au_lname", adVarChar, adParaminput, 20, "M")


  '-- define the first parameter - the one the procedure will return
  '-- the calls are:
  '--   CmdSP.Parameters.Append: append this parameter to the collection for this command object
  '--   CmdSP.CreateParameter(): creates the parameter using the values given:
  '--      "@intID" is the name of the parameter for later reference
  '--      adInteger (value = 3) indicates this parameter is an integer datatype
  '--      adParamInput (value = 1) indicates this parameter is for input
  '--      the blank is a failure to declare the size of the variable
  '--      iVal is an arbitrary initial value for this parameter, placed with the variable
 
  CmdSP.Parameters.Append CmdSP.CreateParameter("@intID", adInteger, adParamInput, , iVal)


  '-- define the first parameter - the one the procedure will return
  '-- the calls are:
  '--   CmdSP.Parameters.Append: append this parameter to the collection for this command object
  '--   CmdSP.CreateParameter(): creates the parameter using the values given:
  '--      "@intIDOut" is the name of the parameter for later reference
  '--      adInteger (value = 3) indicates this parameter is an integer datatype
  '--      adParamOutput (value = 2) indicates this parameter is expected to return an output
  '--      oVal is an arbitrary initial value for this parameter, placed with the variable oVal
 
  CmdSP.Parameters.Append CmdSP.CreateParameter("@intIDOut", adInteger, adParamOutput, oVal)
 
 
  '-- execute the command
  Set adoRS = CmdSP.Execute


'-- loop through the returned recordset
While Not adoRS.EOF

  '-- loop through the field collection, reporting name and contents
  for each adoField in adoRS.Fields
    Response.Write adoField.Name & "=" & adoField.Value & "<br>" & vbCRLF
  Next
  Response.Write "<br>"
  adoRS.MoveNext
Wend


'-- move to the parameter recordset
Set adoRS = adoRS.NextRecordset


'-- report parameter values, accessing each by name
Response.Write "<p>@intIDOut = " & CmdSP.Parameters("@intIDOut").Value & "</p>"
Response.Write "<p>Return value = " & CmdSP.Parameters("RETURN_VALUE").Value & "</p>"


'-- tidy up the handles
Set adoRS = nothing
Set CmdSP.ActiveConnection = nothing
Set CmdSP = nothing
%>