CODEWITHSUNDEEP
sqlstored-proceduresdb2ibm-midrange
user1806890
user1806890

Reputation: 57

IBM i (AS400) Calling SQL Stored Procedure from STRSQL

I’m newbie to create DB2 for IBM i (AS400) Stored procedure. I'm seeking an answer for what’s wrong with my calling stored procedure from STRSQL. Any ‘IN’ parameter stored procedures are callable, but ‘OUT’ parameter stored procedures are not.

create procedure egg(out pcount# INT)
language sql
set option dbgview=*source, USRPRF=*USER
begin
   set pcount# = 5;
end

I call this,

call egg(?)

Then this error shows up.

SQL0418
Message . . . . :   Use of parameter marker not valid.

I want to see the pcount# result, '5', in the line. Any help would be appreciated.

Upvotes: 1

Views: 7461

Answers (1)

Charles
Charles

Reputation: 23783

What you are trying to do will work, but only if you use iNav's Run SQL Scripts query tool..

[ Thu Mar 26 08:50:52 EDT 2015 ]  Run Selected

> call egg(?)

Return Code = 0

Output Parameter #1 = 5

Statement ran successfully   (0 ms)

Another option if you're on a recent (7.1+) release, is the use of global variables..

create or replace variable myout int default(0)  
call egg(myout)  
select myout from sysibm.sysdummy1

Note that even in the scenario of using a global variable, iNav's Run SQL Scripts is a better choice as it has a tab you can open to create, update, delete global variables directly.

Upvotes: 1

Related Questions