Stored Proc Return Values

[VBn00b]

Hi Guys,

I'm busy with a script and have decided to call a sproc and return the result to VB.

Here is the sproc:

CREATE PROCEDURE pr_Cleanup
BEGIN TRAN

TRUNCATE TABLE tb_Users
IF @@ERROR <> 0
BEGIN
ROLLBACK TRAN
RETURN 101
END

TRUNCATE TABLE tb_Excluded
IF @@ERROR <> 0
BEGIN
ROLLBACK TRAN
RETURN 102
END

COMMIT TRAN
RETURN 0

So now I would like to catch that 0, 101 or 102 value. In VB I have the following:

Set objCommand = CreateObject("ADODB.Command")
Set objParam = CreateObject("ADODB.Parameter")
Set objRSSQL = CreateObject("ADODB.Recordset")

objCommand.ActiveConnection = "Driver={SQL Server}; Server=127.0.0.1; Database=ListOfUsers; UID=test; PWD=test"

objCommand.CommandText = "pr_Cleanup"
objCommand.CommandType = 4

set objRSSQL = objCommand.Execute

' Set objParam = objCommand.CreateParameter("@retVal",2,3,4)
' objCommand.Parameters.Append objParam

intSprocValue = objCommand.Parameters("@retVal").value
msgbox intSprocValue


If I run the above it works fine and truncates the tables. How do I go about catching the 0, 101 or 102 error code returned from SQL? The bold parameter line seems to be the issue.

Thanks

 

[dilettante]

Perhaps try removing the @-sign from the parameter name?

 

[VBn00b]

I removed them and I got the following error on the same line of code I just edited:

"Arguments are of the wrong type, are out of acceptable range, or in conflict with one another"

Thanks

 

[VBn00b]

Sorry I edited the wrong line. I removed the @ signs and it does the same as it did before. It executes the stored proc and runs but it does not return the number 0, 101 or 201.

 

[VBn00b]

I've even tried:

Set objParam = objCommand.CreateParameter("test",3,2,5)
objCommand.Parameters.Append objParam
msgbox objParam
intSprocValue = objCommand.Parameters("test").value
msgbox intSprocValue

Still no return value. Please someone help me out

 

[tsuji]

Do this?
>intSprocValue = objCommand.Parameters("@retVal").value
[tt]intSprocValue = objCommand.Parameters("ReturnValue").value[/tt]

You can always use .refresh method of the command object (risk of performance hit). After that you can retrieve the parameters collection (.parameters) and to discover each paramter's name (.name), direction (.direction), precision (.precision), size (.size) and value (.value). This way of auto-discovery can save you from noting the paramter's spec and in particular the name when they are not available handy.

 

[tsuji]

Amendment
I meant Return_Value!
[tt] intSprocValue = objCommand.Parameters("Return[red]_[/red]Value").value[/tt]

In any case, the return value is always indexed 0 in the parameters collection, so it can as well be done like this.
[tt] intSprocValue = objCommand.Parameters(0).value[/tt]