sending sql statment to SQL stored procedure

Alex

unread,

Mar 28, 2002, 1:26:41 PM3/28/02

to

I am building Sql stored procedures using Stored procedure builder

can I buld somthing like

CREATE PROCEDURE ADMIN.Proc1 (SQLString varchar(2000))
RESULT SETS 1
LANGUAGE SQL
------------------------------------------------------------------------
-- SQL Stored Procedure
------------------------------------------------------------------------
P1: BEGIN
-- Declare cursor
DECLARE cursor1 CURSOR WITH RETURN FOR
SQLString;
-- Cursor left open for client application
OPEN cursor1;

END P1

If yes ,please show example , how to do it.

this is example of SQL SERVER code that I am trying to repproduce in
DB2 stored procedure.
-----
step 1
create procedure sp_ExecMySql @x varchar(1000)
as
exec(@x)

step 2

declare @y varchar(1000)
set @y = 'Select a,b,c from d'

exec sp_ExecMySql @y

---
Thank you

Serge Rielau

unread,

Mar 28, 2002, 2:08:43 PM3/28/02

to

Hi Alex,

Try this (not tested):

CREATE PROCEDURE ADMIN.Proc1 (SQLString varchar(2000))
RESULT SETS 1
LANGUAGE SQL
------------------------------------------------------------------------
-- SQL Stored Procedure
------------------------------------------------------------------------
P1: BEGIN
-- Declare cursor

DECLARE cursor1 CURSOR WITH RETURN FOR s1;
PREPARE s1 FROM SQLString;

-- Cursor left open for client application
OPEN cursor1;

END P1

--
Serge Rielau
DB2 UDB SQL Compiler Development
IBM Software Lab, Canada

Alex

unread,

Mar 28, 2002, 4:46:41 PM3/28/02

to

Serge, thank you very much you code is it working fine.

have a great weekend

Serge Rielau <sri...@ca.ibm.com> wrote in message news:<3CA36A3A...@ca.ibm.com>...

Dirk "db2scout" Wollscheid

unread,

Mar 28, 2002, 7:41:24 PM3/28/02

to

That's cool. Didn't know you could do that. Serge, you should write a
developerworks article with all the tricks you keep dispersing here!

Dirk

"Serge Rielau" <sri...@ca.ibm.com> wrote in message
news:3CA36A3A...@ca.ibm.com...

Serge Rielau

unread,

Mar 31, 2002, 1:15:01 PM3/31/02

to

I'm a ghostwriter...

Cheers
Serge

Alex

unread,

Apr 1, 2002, 12:48:57 PM4/1/02

to

Serge one more tric on How ..
I use you example ....

CREATE PROCEDURE ADMIN.test1(SQLString varchar(2000))

RESULT SETS 1
LANGUAGE SQL
------------------------------------------------------------------------
-- SQL Stored Procedure
------------------------------------------------------------------------
P1: BEGIN

-- Declare cursor
DECLARE cursor1 CURSOR WITH RETURN FOR s1;
PREPARE s1 FROM SQLString;
-- Cursor left open for client application
OPEN cursor1;

END P1

it work fine with select like
call admin.test1('select tabname from syscat.table)

but when I try use
call admin.test1('insert into admin.boom(a,b) values(1,2))
I am getting message

SQL0517N The cursor "CURS3" identifies a prepared statement that is
not a
SELECT or VALUES statement. SQLSTATE=07005

1. How submit insert with my stored procedure ?
2. Can we return code to caller from stored procedure and continue
execution of stored procedure ?
3. I found course in Toronto - (CF71C- DB2 Stored procedures
progarmming)
is it good course to opinion ?

Thank you very much for you help
Alex

Serge Rielau <sri...@ca.ibm.com> wrote in message news:<3CA75225...@ca.ibm.com>...

Serge Rielau

unread,

Apr 1, 2002, 4:03:39 PM4/1/02

to

Hi Alex,

You can't open a cursor on UPDATE, DELETE or INSERT.
(We're slowly warming the SQL Standrad comittee to that thought, but it's like thawing an
iceberg ;-)

What about this (untested as always):

CREATE PROCEDURE ADMIN.test1(SQLString varchar(2000))
RESULT SETS 1
LANGUAGE SQL
------------------------------------------------------------------------
-- SQL Stored Procedure
------------------------------------------------------------------------
P1: BEGIN

-- a bit wasteful, shoot me
IF substr(SQLString, 1, 6) = 'INSERT' OR
substr(SQLString, 1, 6) = 'DELETE' OR
substr(SQLString, 1, 6) = 'UPDATE')
THEN
EXECUTE IMMEDIATE SQLString;
ELSE
-- Need to push another compound here because of DECLARE

BEGIN
-- Declare cursor
DECLARE cursor1 CURSOR WITH RETURN FOR s1;
PREPARE s1 FROM SQLString;
-- Cursor left open for client application
OPEN cursor1;

END;
END IF;
END P1

I don't know how good any classes are since I never attended them.
Heck - I'm not even certified..... :-)

Alex

unread,

Apr 2, 2002, 3:27:35 PM4/2/02

to

Works just fine.

Thank you very much.

Alex

Serge Rielau <sri...@ca.ibm.com> wrote in message news:<3CA8CB2B...@ca.ibm.com>...