Some examples of the storage process being invoked in ASP. (1)

Procedure return Values How-to Execute a Stored Proc ' s
This demo It's called Returnvalue.asp and shows to execute a stored procedure the has input params, output params , a returned recordset and a return value.

<!--Author:john Bailey-->


<%@ Language=vbscript%>

<%
' CODE to CREATE ' The STORED PROCEDURE so this ASP accesses
' Just Remove all comments on this line, paste into the SQL Query Analyzer and run.

'--Insures 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 a 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=;D Atabase=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 Parameter-the one, the procedure would 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 was an integer datatype
'--adparamreturnvalue (value = 4) indicates this parameter was expected to be returned
'-4 is a arbitrary initial value for this parameter

CmdSP.Parameters.Append cmdsp.createparameter ("Return_value", Adinteger, adParamReturnValue, 4)


'--Define the Parameter-the one, the procedure would 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 =) indicates this parameter is a string datatype
'--adparaminput (value = 1) indicates this parameter was for input
'-is the ' size ' of the ' string in characters
'--' M ' is a arbitrary initial value for this parameter

CmdSP.Parameters.Append cmdsp.createparameter ("@au_lname", adVarChar, adParamInput, "M")


'--Define the Parameter-the one, the procedure would return
'--The calls are:
'--CmdSP.Parameters.Append:append This parameter to the collection for this Command object
'--Cmdsp.createparameter (): Creates the parameter using the Valu