• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 2090
  • Last Modified:

VBScript calling SQL Server stored procedure with OUTPUT parameter

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:

Dim RetVal
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?

Thanks!
0
wlevy
Asked:
wlevy
  • 2
1 Solution
 
azserrataCommented:
Hello

Take a look here

http://authors.aspalliance.com/stevesmith/articles/sprocs.asp

specially point 4 about ("Any time you need to get data back from a stored procedure via a parameter")

Cheers
azserrata
0
 
wlevyAuthor Commented:
Thank you azserrata that is exactly what I needed.
0
 
azserrataCommented:
Great!
0

Featured Post

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now