VBScript calling SQL Server stored procedure with OUTPUT parameter
Posted on 2006-11-22
Hi - I have a VBS file that I use to execute a SQL Server stored procedure, but I want to enhance it by retrieving a "Status" indicator when the stored proc ends. I don't think I can use a RETURN value in the stored proc so I added an OUTPUT parameter.
The stored proc has two input parameters and one output parameter. When it had just the two input parameters I called in from VBS like this:
NOTE: cn is an ADODB connection object.
cn.Execute("MyProc 'N', 1)
That works fine. With the new output parameter (@RETURN_STATUS INTEGER OUTPUT), I am trying to call the stored proc like this:
RetVal = 99
cn.Execute("MyProc 'N', 1, @RETURN_STATUS = " & RetVal & " OUTPUT")
I get the following error message:
[Microsoft][ODBC SQL Server Driver][SQL Server]Cannot use the OUTPUT option when passing a constant to a stored procedure.
NOTE: I tested the stored proc in SQL Server like this:
declare @rtn integer
exec MyProc 'N',1,@RETURN_STATUS = @rtn OUTPUT
print 'Return value = ' + cast(@rtn as varchar)
Can anyone tell me how I can call the stored proc using the connection object and get the value of the output parameter?
Do I have to use a command object?