SQL Stored procedurefrom vbscript

[kev05]

I need to run a stored procedure. However, I’m getting some difficulties.

From SQL server, I run the following code below and I’m getting an integer number as a result. Either ‘0’ or ‘2’


declare @Cur datetime

set @Cur = GetUTCDate()

print dbo.ADMINTOOL_fn_GetExchangeStatus(1, @Cur)




What I want to do is execute the above from VBScript and then fetch the result of the stored procedure.



It seems that


dbo.ADMINTOOL_fn_GetExchangeStatus(1, @Cur) from the above code is a function within the stored procedure.



The stored procedure name is: dbo.ADMINTOOL_GetExchangeStatusData and return the results in xml ( from what I gathered).



The code that I’m executing is:



DBserver = "1.1.1.1"
Set cn =CreateObject("ADODB.Connection")
cn.Open "DRIVER={SQL Server};server="&DBserver&";database=XXX;uid=XXX;pwd=XXX"
Set cmd = CreateObject("ADODB.Command")
Set cmd.ActiveConnection = cn
cmd.CommandText = "{?=dbo.ADMINTOOL_GetExchangeStatusData(?,?) }"
cmd.Parameters.Append cmd.CreateParameter("RetVal", adInteger, adParamReturnValue)
cmd.Parameters.Append cmd.CreateParameter("Param1", adInteger, adParamInput)
cmd.Parameters("Param1") = 6
cmd.Parameters.Append cmd.CreateParameter("Param2", adChar, adParamInput)
cmd.Parameters("Param2") = GetUTCDate()
cmd.Execute
MsgBox cmd("RetVal")



What I believe is wrong in the above is:

cmd.CommandText = "{?=dbo.ADMINTOOL_GetExchangeStatusData(?,?) }"


What I think I need in there is the name of the stored procedure but I somehow need to wrap the function to the stored procedure as I need the function to execute the stored procedure. This is what I’m not sure how to do.


If by any chance, you know how to do that,

Please let me know,



Thanks,

Kev

 

[AccordingToDale]

Here's a snippet I use - it's a SQL connection rather ADODB, but you'll get the gist....

'create command object - tell which procedure to run
Dim com As New SqlClient.SqlCommand("storedProcedureName", connectionObject)
'assign type 
com.CommandType = CommandType.StoredProcedure
'add parameters
com.Parameters.Add("@spVariableName1", valueToPass1)
com.Parameters.Add("@spVariableName2", valueToPass2)
Try
  'return the value of the stored procedure 
  'i'm  not using an output parameter, just a return value
  Return com.ExecuteNonQuery.ToString
Catch ex As Exception
  'if there is an error i return zero
  Return 0
Finally
  'cleanup
  com.Dispose()
End Try

ADODB is pretty similar I believe.

Dale

 

[bboffin]

There are two ways to get an SP to return a value to you.

If there's only one integer value to be returned you can use the RETURN value from the SP.

Your SP should look like:

CREATE PROCEDURE GetExchangeStatus
AS
RETURN dbo.ADMINTOOL_GetExchangeStatusData(1,GetUTCDate())

In your code you have to declare a parameter to receive the value and assign that parameter the correct 'direction' property. Your code shows that you have in fact done this so following the execution of the SP the value 1 or 2 should be the value of parameter "RetVal".

Thus your code becomes:

DBserver = "1.1.1.1"
Set cn =CreateObject("ADODB.Connection")
cn.Open "DRIVER={SQL Server};server="&DBserver&";database=XXX;uid=XXX;pwd=XXX"
Set cmd = CreateObject("ADODB.Command")
Set cmd.ActiveConnection = cn
cmd.CommandText = "GetExchangeStatus"
cmd.Parameters.Append cmd.CreateParameter("RetVal", adInteger, adParamReturnValue)
cmd.Execute
MsgBox cmd.Parameters("RetVal")

The other way is to use OUTPUT parameters. An OUTPUT parameter is used to return a value to the caller. For example your SP would be changed to:

CREATE PROCEDURE GetExchangeStatus
@Result int OUTPUT
AS
SET @Result = dbo.ADMINTOOL_GetExchangeStatusData(1,GetUTCDate())

and your code would become:

DBserver = "1.1.1.1"
Set cn =CreateObject("ADODB.Connection")
cn.Open "DRIVER={SQL Server};server="&DBserver&";database=XXX;uid=XXX;pwd=XXX"
Set cmd = CreateObject("ADODB.Command")
Set cmd.ActiveConnection = cn
cmd.CommandText = "GetExchangeStatus"
cmd.Parameters.Append cmd.CreateParameter("@Result", adInteger, adParamOutput)
cmd.Execute
MsgBox cmd.Parameters("@Result")

You may have multiple output parameters which can be of any valid datatype and still leave the return value for its intended use which is to give an indication of the success or otherwise of the SP.


Bob Boffin