Getting the output of a stored procedure in a VBScript

Discussion:

(too old to reply)

fouad me

2003-09-14 08:21:37 UTC

I am running a stored procedure that needs parameters through a VBScript and
I need to know to get the output from that procedure to a variable in the
script.
Here is how am I calling the stored procedure:

Dim cnn
Set cnn = CreateObject("ADODB.Connection")
cnn.Open "Provider=SQLOLEDB;Data Source=myServer;Initial
Catalog=myDB;Integrated Security=SSPI;Persist Security Info=False;"
cnn.Execute "exec execute_job_with_return 'base' ,'00020' ,5 ,'ubi' ,'UC',
'loyalty' , 0, '000' , lty_rmmeltest ,'smart' ,'%DATA%\txn TEST.txt',' ' ,
'Y' ,NULL"

The stored procedure then returns an integer value (named "runid") which i
need to use for further operations.

Thanks in advance.

fouad me

2003-09-14 08:46:38 UTC

Permalink

It's not a recordset, it's just an integer value.

is that the RETURN value that you are trying to capture? Or a Recordset or
an OUTPUT parameter?
This is more of a VBScript/ASP question. So, you might want to post it to

VBScript/ASP newsgroup.
Anyway, you will most probably find an answer at aspfaq.com
--
HTH,
Vyas, MVP (SQL Server)
http://vyaskn.tripod.com/
What hardware is your SQL Server running on?
http://vyaskn.tripod.com/poll.htm
I am running a stored procedure that needs parameters through a VBScript and
I need to know to get the output from that procedure to a variable in the
script.
Dim cnn
Set cnn = CreateObject("ADODB.Connection")
cnn.Open "Provider=SQLOLEDB;Data Source=myServer;Initial
Catalog=myDB;Integrated Security=SSPI;Persist Security Info=False;"
cnn.Execute "exec execute_job_with_return 'base' ,'00020' ,5 ,'ubi' ,'UC',
'loyalty' , 0, '000' , lty_rmmeltest ,'smart' ,'%DATA%\txn TEST.txt',' ' ,
'Y' ,NULL"
The stored procedure then returns an integer value (named "runid") which i
need to use for further operations.
Thanks in advance.

2003-09-14 08:51:25 UTC

Permalink

dim rs
set rs = cnn.execute(strSQL)

'data
do until rs.eof
msgbox rs.fields(0).name & ": " & rs.fields(0).value
rs.movenext
loop
--
-oj
Rac v2.2 & QALite!
http://www.rac4sql.net

Post by fouad me
I am running a stored procedure that needs parameters through a VBScript and
I need to know to get the output from that procedure to a variable in the
script.
Dim cnn
Set cnn = CreateObject("ADODB.Connection")
cnn.Open "Provider=SQLOLEDB;Data Source=myServer;Initial
Catalog=myDB;Integrated Security=SSPI;Persist Security Info=False;"
cnn.Execute "exec execute_job_with_return 'base' ,'00020' ,5 ,'ubi' ,'UC',
'loyalty' , 0, '000' , lty_rmmeltest ,'smart' ,'%DATA%\txn TEST.txt',' ' ,
'Y' ,NULL"
The stored procedure then returns an integer value (named "runid") which i
need to use for further operations.
Thanks in advance.

fouad me

2003-09-14 11:10:37 UTC

Permalink

I tried that but it keeps giving me:

"Item cannot be found in the collection corresponding to the requested name
or ordinal."
Why is that?

NOTE: When i run the below procedure, the retured value comes with the
column name (No Column Name)

Post by oj
dim rs
set rs = cnn.execute(strSQL)
'data
do until rs.eof
msgbox rs.fields(0).name & ": " & rs.fields(0).value
rs.movenext
loop
--
-oj
Rac v2.2 & QALite!
http://www.rac4sql.net

Post by fouad me
I am running a stored procedure that needs parameters through a VBScript

and

Post by fouad me
I need to know to get the output from that procedure to a variable in the
script.
Dim cnn
Set cnn = CreateObject("ADODB.Connection")
cnn.Open "Provider=SQLOLEDB;Data Source=myServer;Initial
Catalog=myDB;Integrated Security=SSPI;Persist Security Info=False;"
cnn.Execute "exec execute_job_with_return 'base' ,'00020' ,5 ,'ubi' ,'UC',
'loyalty' , 0, '000' , lty_rmmeltest ,'smart' ,'%DATA%\txn TEST.txt',' ' ,
'Y' ,NULL"
The stored procedure then returns an integer value (named "runid") which i
need to use for further operations.
Thanks in advance.

Dan Guzman

2003-09-14 14:03:25 UTC

Permalink

There are 3 methods to pass data from a stored procedure: recordset,
output parameter and return value. The example below shows how to all
three techniques. Note that you'll need to use a command object to
access stored procedure parameters and return values.

ALTER PROCEDURE MyProcedure
@runid int OUTPUT
AS
SET NOCOUNT ON
SELECT 1 -- first column in recordset, no column name
SET @runid = 2 -- @runid OUTPUT parameter
RETURN 3 -- @@RETURN_VALUE parameter
GO

' init and execute command object
Set command = CreateObject("ADODB.Command")
Set command.ActiveConnection = cnn
command.CommandType = 4 'adCmdStoredProcedure
command.CommandText = "MyProcedure"
command.Parameters.Refresh
command.Execute

MsgBox "Recordset value=" & _
recordset.Fields(0).Value

MsgBox "Output parameter value for @runid=" & _
command.Parameters("@runid").Value

MsgBox "Return value=" & _
command.Parameters("@RETURN_VALUE").Value
--
Hope this helps.

Dan Guzman
SQL Server MVP

-----------------------
SQL FAQ links (courtesy Neil Pike):

http://www.ntfaq.com/Articles/Index.cfm?DepartmentID=800
http://www.sqlserverfaq.com
http://www.mssqlserver.com/faq
-----------------------

Post by fouad me
"Item cannot be found in the collection corresponding to the requested name
or ordinal."
Why is that?
NOTE: When i run the below procedure, the retured value comes with the
column name (No Column Name)

Post by oj
dim rs
set rs = cnn.execute(strSQL)
'data
do until rs.eof
msgbox rs.fields(0).name & ": " & rs.fields(0).value
rs.movenext
loop
--
-oj
Rac v2.2 & QALite!
http://www.rac4sql.net

Post by fouad me
I am running a stored procedure that needs parameters through a VBScript

and

Post by fouad me
I need to know to get the output from that procedure to a variable