fict1080 fict1080 - 1 year ago 76
SQL Question

How should I select or display the out variable from a stored procedure call?

I'm writing a pretty basic stored procedure that just takes values from the sample DB2 database and computes the standard deviation. I wrote the procedure itself just fine, and I can call it without error. But I can't figure out how to actually display my result or select it in a statement. Everything I try results in a syntax error and I haven't been able to find anyone doing this specific task in my google searches.

This is the gist of my code (snipped for brevity):

(OUT std_dev real)
--do stuff
SET std_dev = 10; --changed for simplicity


All this runs, but just CALL doesn't create any output. What's the syntax to SELECT the out variable? I can't put a DECLARE before the CALL because it's not in a stored procedure, and PRINT doesn't work either.

(@ is my terminal character because I'm using ; in the stored procedure)

Edit: Both the create procedure and call statements are made in the same SQL file, the database is connect to through localhost and I'm using DB2 and developing in IBM Data Studio 4.1.2.

Answer Source

From wherever the CALL is being made, that feature might present a Result Set, despite apparently not presenting the result of an OUT parameter. If so, then the stored procedure perhaps could be revised to return the OUT value [instead, or additionally] as a result set, so that the interface that accepts the CALL statement as input, might present that result-set. Regardless:

In a statement processor [e.g. that is not a GUI, but] for which SELECT query output is presented, the following scripted requests should likely suffice:

create variable my_real real
call SAL_STD_DEV(my_real)
select my_real from sysibm.sysdummy1
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download