get a return from a SP?

[__Stephen]

I have an SP with an output param

This code works fine in T-SQL
declare @ret varchar(8)
exec dbo.spProcessSecurity 'L\M{¢!AA', @ret OUTPUT
--print @ret

How do I grab the @ret in vb(6) ?

TIA

 

[zacks]

I have an SP with an output param

This code works fine in T-SQL
declare @ret varchar(8)
exec dbo.spProcessSecurity 'L\M{¢!AA', @ret OUTPUT
--print @ret

How do I grab the @ret in vb(6) ?

TIA

WARNING!! UNTESTED CODE!!

Dim cn As New rdoConnection
Dim rst As New rdoConnection
Dim sConnect As String
Dim sSQL As String
Dim qry As rdoQuery

sConnect = "UID=sa;PWD=blah;Database=msdb;Server=server1;Driver={SQL
Server};DSN='';"
With cn
.Connect = sConnect
.LoginTimeout = 0
.CursorDriver = rdUseOdbc
.QueryTimeout = 0
.EstablishConnection rdDriverNoPrompt
End With

sSQL = "{ ? = call sp_storedprocname (?, ?, ?, ?, ?, ?) }"
Set qry = cn.CreateQuery("startjob", sSQL$)
qry.rdoParameters(0).Direction = rdParamReturnValue
qry(1) = "test job"
qry(5) = "no step"
qry.Execute
If qry.rdoParameters(0) <> 0 Then
MsgBox "start job returned error!"
End If
cn.Close

 

[__Stephen]

thanks.

I have an SP with an output param

This code works fine in T-SQL
declare @ret varchar(8)
exec dbo.spProcessSecurity 'L\M{¢!AA', @ret OUTPUT
--print @ret

How do I grab the @ret in vb(6) ?

TIA

WARNING!! UNTESTED CODE!!

Dim cn As New rdoConnection
Dim rst As New rdoConnection
Dim sConnect As String
Dim sSQL As String
Dim qry As rdoQuery

sConnect = "UID=sa;PWD=blah;Database=msdb;Server=server1;Driver={SQL
Server};DSN='';"
With cn
.Connect = sConnect
.LoginTimeout = 0
.CursorDriver = rdUseOdbc
.QueryTimeout = 0
.EstablishConnection rdDriverNoPrompt
End With

sSQL = "{ ? = call sp_storedprocname (?, ?, ?, ?, ?, ?) }"
Set qry = cn.CreateQuery("startjob", sSQL$)
qry.rdoParameters(0).Direction = rdParamReturnValue
qry(1) = "test job"
qry(5) = "no step"
qry.Execute
If qry.rdoParameters(0) <> 0 Then
MsgBox "start job returned error!"
End If
cn.Close

 

[Guest]

you could also use RETURN function to return a value from your stored
procedure....

 

[__Stephen]

you could also use RETURN function to return a value from your stored
procedure....

Return only returns an int value, I wanted varchar.