Capturing return code from sp

Question

Post reply

Capturing return code from sp

Bob Clayton

Old Hand

Points: 362
More actions
June 14, 2005 at 12:59 pm

#362891

I have a stored procedure that has no parameters. I need to run this using vbscript using ADO. I'm having trouble coming up with the syntax to capture the return code from the stored procedure. Here is a snippet of some of my code. I just need the commands to retrieve the return code from the stored procedure, after it executes. Can anyone provide example code?
lo_Cmd.ActiveConnection = lo_Conn
lo_Cmd.CommandText = "proc_test_return_code"
lo_Cmd.CommandType = 4
lo_Cmd.CommandTimeout = 7200
' Run the stored procedure.
lo_Cmd.Execute ()

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 1

Here's the code I use in vb for such a situation

Private Function exec_AutoExecPinTableNumbers(Optional ByRef ReturnValue As Integer) As Integer

On Error GoTo Gestion

Dim MyCmd As ADODB.Command

Set MyCmd = New ADODB.Command

MyCmd.CommandText = "dbo.AutoExecPinTableNumbers"

MyCmd.CommandType = adCmdStoredProc

Dim MyParam As ADODB.Parameter

Set MyParam = New ADODB.Parameter

MyParam.Direction = adParamReturnValue

MyParam.Name = "@Return"

MyParam.Type = adInteger

MyCmd.Parameters.Append MyParam

MyCn.Open

MyCmd.ActiveConnection = MyCn

MyCmd.Execute exec_AutoExecPinTableNumbers

MyCn.Close

ReturnValue = CInt(MyCmd.Parameters("@Return").Value)

Set MyParam = Nothing

Set MyCmd = Nothing

Exit Function

Gestion:

ErrHandler ModuleName, Me.Name, "exec_AutoExecPinTableNumbers", Err

MsgBox Err.Description & " : " & Err.Number

End Function

Just post back if you have questions.

Bob Clayton Old Hand Points: 362 More actions · Answer 2

Does anyone have an example that is vbscript, not pure vb? All the examples I find are vb, and nothing I'm trying is working.

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 3

Can you post what you have... will be faster to correct that than to find a vbs version.

DSP-120308 Hall of Fame Points: 3959 More actions · Answer 4

I believe that if you make a few modifications to Remi's code like this:

Dim MyCmd

Set MyCmd = CreateObject("ADODB.Command")

And do the same thing for each Dim/Set referencing the ADODB objects.

Good Luck

Darrell

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 5

Actually, he'l have to remove all the As DataType, use createobject instead of set and change the enumeration constants to their actual values. It's not hard to do, it's just long.

Bob Clayton Old Hand Points: 362 More actions · Answer 6

I finally threw in the towel, and added an output parameter with the return code, because I already have code that works to get a value from an output parameter.

One thing that drives me nuts about Microsoft is that things that should be easy, like running a stored procedure from vb, vbscript, MS Access, etc... is made absurdly complicated. Really, this is such a commonly needed thing, that they should make it so you need at most, 3 lines of code to run a stored procedure. I used PowerBuilder many years ago, and they made all of the commonly required database tasks super easy. Microsoft seems to try to make things as labor-intensive as possible. Ok, enough ranting for now...