Stored Procedure output parameter

[Pete Regan]

I am having trouble with the syntax for writing to a variable when executing a stored procedure that returns an output parameter in xbasic. My syntax is as follows, but I keep getting syntax errors when it tries to execute. Has anyone figure out how to work with output parameters of sql stored procedures?

Dim cn as SQL::Connection
dim rs as SQL::ResultSet
dim sql_stmt as C
dim UserID As C
dim Authorized As N
dim args as sql::arguments

args.add("UserID", UserID)
args.add("Authorized", Authorized, sql::ArgumentUsage::InputOutputArgument)

sql_stmt = "genO2CleaningAuthorization @UserID = :UserID, @Authorized = :Authorized output"
cn.execute(sql_stmt,args)


Any help would be greatly appreciated.

[glenschild]

Re: Stored Procedure output parameter

Hi Pete

Depends what backend version of sql you are using but you are missing the sql command to fire the procedure

sql_stmt = "[COLOR=#FF0000]XXXXXX[/COLOR] genO2CleaningAuthorization @UserID = :UserID, @Authorized = :Authorized output"

If MS SQL this should be EXEC, MySQL is CALL, there are others....

[mumfie]

Re: Stored Procedure output parameter

Any help would be greatly appreciated.

This thread contains an example using Microsoft SQL Server 2008.
http://msgboard.alphasoftware.com/al...-from-a-button


e.g.
argz2..set("argJobno",e._currentRowKeys[1])
argz2.set("result",result,SQL::ArgumentUsage::InputOutputArgument)

vcall="{call recalc_dates_and_priorities(:argJobno, :result)} "


OK = cnx2.execute(vCall,argz2)
if ok
result=argz2.find("result").data
end if

String Output parameters have to be padded to a specific length before passing them and to access the return value we had to re-find them.

[Pete Regan]

Re: Stored Procedure output parameter

Thanks for your help. It turns out that using "output" at the end of the procedure is not required and causes a syntax error. Once I removed "output" the stored procedure executed, then I had to use args.find("Authorized").data to get the result. Also, EXEC is implied and is not needed to execute the procedure.

Anyway the final code to run the procedure is:
sql_stmt = "genO2CleaningAuthorization :UserID, :Authorized"
cn.execute(sql_stmt,args)

[glenschild]

Re: Stored Procedure output parameter

Thanks for the update.

[Garry Flanigan]

Re: Stored Procedure output parameter

I am trying to execute a stored procedure and get back a result that I can use in further processsing.

args.set("arg1","some value")
args.set("arg2",arg2,SQL::ArgumentUsage::InputOutputArgument)

OK = cn.execute("EXEC mySP :arg1, :arg2",args)
if OK
arg2 = args.find("arg2").data
end if

String Output parameters have to be padded to a specific length before passing them and to access the return value we had to re-find them.

I am using SQL Server 2008 and trying to get the value of a character output variable.
When I execute in Server Management Studio I get the output as expected but in AA I always get arg2 = "".
I tried padding to 50 long, which is the variable declared length.
I also tried:
OK = cn.execute("{CALL mySP(:arg1, arg2)}",args)
but all to no avail.
Any ideas?

[mumfie]

Re: Stored Procedure output parameter

Any ideas?

Have you run the code in debug/verified the result of cn.execute ?.
If it returns false you can check error details in cn.callResult.Text

If the result is true you might need to post the code for mySP

[Garry Flanigan]

Re: Stored Procedure output parameter

Hi Colin,
I am doing this in the debugger and when this runs
OK = cn.execute("EXEC mySP :arg1, :arg2",args)
it returns .t.

You mention:

String Output parameters have to be padded to a specific length before passing them and to access the return value we had to re-find them

Can you be more specific?

[mumfie]

Re: Stored Procedure output parameter

Can you be more specific?

Yes.
We had to pad the output parameters before calling the stored procedure
E.G.
arg2 = padr(arg2,50," ")
The following line of code should re-find the output value after calling the stored procedure
arg2 = args.find("arg2").data

[Garry Flanigan]

Re: Stored Procedure output parameter

Well, I give up (for the moment).
It all works in SMS and I get the character output variable as expected but I am guessing there there is some quirk with the SQL Server version I am using (10.50.2550) that is causing problems with AA.
Getting messages back from stored procedures is essential for the app I am developing. I do not want to run these SP's in AA as I want to offload the work to SQL Server.

[Garry Flanigan]

Re: Stored Procedure output parameter

Colin,
Everything you have said has proved to be correct and works well.
The problem in my case is that I had not put:

SET NOCOUNT ON;

in the stored procedure.
My thanks also go to Kurt at Alpha for his assistance in helping get this sorted out.

[mumfie]

Re: Stored Procedure output parameter

My thanks also go to Kurt at Alpha for his assistance in helping get this sorted out.

Hi Garry,
When we started using output parameters back in alpha5 v10 we could not get then to work in MSSQL either.
Apparently it was due to the way output parameters work in MS SQL.
Kurt made some internal changes to alpha5 v10/v11 and provided the sample code to refind the parameters which is what my example code was based on.
Anyway it is good to know you managed to get it working.

[jwilliamson]

Re: Stored Procedure output parameter

I know this is a dead thread, but in case anyone else is looking at this thread I found that adding SET NOCOUNT ON; fixed the issue for me as well. If your stored procedure is executing properly but you are not getting an output parameter try this!