Output parameters

[Mike P]

I am trying to return an output parameter to my code on executing a
stored procedure. In Query Analyzer, it works with no problem, but when
I run my ASP code below, the output parameter never seems to return
anything. Can anybody help?

Dim cmdNewCampaign, rsNewCampaign, intNumber
Const adCmdStoredProc = &H0004
Const adParamInput = &H0001
Const adParamOutput = &H0002
Const adVarChar = 200
Const adInteger = 3

Set cmdNewCampaign = Server.CreateObject ("ADODB.Command")
cmdNewCampaign.ActiveConnection = strConnection
cmdNewCampaign.CommandText = "AddNewCampaign"
cmdNewCampaign.CommandType = adCmdStoredProc
cmdNewCampaign.Parameters.Append
cmdNewCampaign.CreateParameter("@CampaignName",adVarChar,adParamInput
,100, request("CampaignName"))
cmdNewCampaign.Parameters.Append
cmdNewCampaign.CreateParameter("@CampaignID",adInteger,adParamOutput)
Set rsNewCampaign = cmdNewCampaign.Execute

intNumber = cmdNewCampaign.Parameters("@CampaignID")

 

[Bob Barrows [MVP]]

I am trying to return an output parameter to my code on executing a
stored procedure. In Query Analyzer, it works with no problem, but
when I run my ASP code below, the output parameter never seems to
return anything. Can anybody help?

Dim cmdNewCampaign, rsNewCampaign, intNumber
Const adCmdStoredProc = &H0004
Const adParamInput = &H0001
Const adParamOutput = &H0002
Const adVarChar = 200
Const adInteger = 3

Set cmdNewCampaign = Server.CreateObject ("ADODB.Command")
cmdNewCampaign.ActiveConnection = strConnection
cmdNewCampaign.CommandText = "AddNewCampaign"
cmdNewCampaign.CommandType = adCmdStoredProc
cmdNewCampaign.Parameters.Append
cmdNewCampaign.CreateParameter("@CampaignName",adVarChar,adParamInput
,100, request("CampaignName"))
cmdNewCampaign.Parameters.Append
cmdNewCampaign.CreateParameter("@CampaignID",adInteger,adParamOutput)
Set rsNewCampaign = cmdNewCampaign.Execute

intNumber = cmdNewCampaign.Parameters("@CampaignID")

1. SQL Server does not send return or output parameter values until all
resultsets generated by the stored procedure are consumed by the caller. It
appears, by your use of "Set rsNewCampaign = cmdNewCampaign.Execute" that
this procedure is intended to return a resultset. This means that you will
not see your output parameter value until you either close the recordset or
retrieve all the records being returned by the procedure (typically done by
navigating to the last record). I will typically use GetRows to pull all
the records into an array, allowing me to close the recordset and get my
output parameter values, but if you want to avoid using an array, and you
need to use the recordset data after retrieving the output value, you will
need to use a client-side cursor (set the recordset's cursorlocation
property to adUseClient).

2. Those informational "x rows were affected" messages that you see in Query
Analyzer are sent to the caller as resultsets. Those resultsets also need to
be consumed before output and return values are sent. You should make a
practice of suppressing those informational messages by including the line
"SET NOCOUNT ON" in every stored procedure that you write ... unless your
application needs those messages.