sending sql statment to SQL stored procedure 
Author Message
 sending sql statment to SQL stored procedure

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

as

step 2




---
Thank you



Tue, 14 Sep 2004 02:26:41 GMT
 sending sql statment to SQL stored procedure

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



Tue, 14 Sep 2004 03:08:43 GMT
 sending sql statment to SQL stored procedure
Serge, thank you very much you code is it working fine.

have a great weekend

Quote:

> 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



Tue, 14 Sep 2004 05:46:41 GMT
 sending sql statment to SQL stored procedure
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


Quote:
> 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



Tue, 14 Sep 2004 08:41:24 GMT
 sending sql statment to SQL stored procedure
I'm a ghostwriter...

Cheers
Serge

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



Fri, 17 Sep 2004 02:15:01 GMT
 sending sql statment to SQL stored procedure
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

Quote:

> I'm a ghostwriter...

> Cheers
> Serge



Sat, 18 Sep 2004 01:48:57 GMT
 sending sql statment to SQL stored procedure
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..... :-)

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



Sat, 18 Sep 2004 05:03:39 GMT
 sending sql statment to SQL stored procedure
Works just fine.

 Thank you very much.

  Alex

Quote:

> 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..... :-)

> Cheers
> Serge



Sun, 19 Sep 2004 04:27:35 GMT
 
 [ 8 post ] 

 Relevant Pages 

1. Sending SQL statement to a stored procedure

2. Using ADO to send image data to a SQL 2000 stored procedure

3. sending parameters from VB 6 to SQL 6.5 stored procedure

4. Can I Send E-Mail using PL/SQL Stored Procedure

5. How to send back error from SQL Store procedure

6. Need help with sql statment in an odbc statment

7. using SQL stored procedure results in stored procedure

8. Tool for converting Store Procedure in SQL Server to Oracle Store Procedures

9. Calling Oracle Stored Procedure from within SQL Server Stored Procedure

10. External Stored Procedure vs. SQL Stored Procedure

11. Calling Java Stored Procedure from SQL Stored Procedure

12. Case Statment in Stored Procedure Creates ADO Error


 
Powered by phpBB® Forum Software