Re: CTE in PROC?

Thanks much Elvis. RH


"Elvis Budimlic"
<ebudimlic@xxxxxxxxxxxxxxxxxxxxxxxxx>
wrote in message news:00f601c76ab2$7cf12590$6e01a8c0@xxxxxxxx

There's no need for dynamic SQL.  Here is a modified example without it:

CREATE PROCEDURE ctetest
RESULT SETS 1
LANGUAGE SQL
BEGIN

DECLARE ctecursor CURSOR FOR
  WITH T1 AS (SELECT 'Testing' FROM SYSIBM/SYSDUMMY1) SELECT * FROM T1;

OPEN ctecursor;

END;

As for differences between MS & DB2, MS tends to do things that deviate

from

standards if they believe it makes sense syntactically etc.
IBM tends to stick with standards, for portability sake mostly.

Good book to read for DB2 stored procedures is "Stored Procedures,

Triggers,

and User-Defined Functions on DB2 Universal Database for iSeries" IBM
redbook.  Download the PDF at:

http://www.redbooks.ibm.com/redbooks/pdfs/sg246503.pdf

Also, check out "Related publications" section in that redbook.

Elvis

Celebrating 10-Years of SQL Performance Excellence

-----Original Message-----
From: midrange-l-bounces@xxxxxxxxxxxx
[mailto:midrange-l-bounces@xxxxxxxxxxxx] On

Behalf Of Ryan Hunt

Sent: Monday, March 19, 2007 7:00 PM
To: midrange-l@xxxxxxxxxxxx
Subject: Re: CTE in PROC?

Thanks Elvis.
I am pretty much an MS SQL Server developer so for every 500 MS SQL procs

I

write, I write 1 for DB2/400.  It always amazes me how differently each

DBMS

implements certain things.  In the MS world, using the SQL_TEXT variable
below is considered dynamic SQL and is typically only used if a parameter

or

variable determined at runtime needs to alter the SQL statement in some
fashion.  Otherwise, static SQL can be used (for the most part).

So, I completely wasn't expecting the SQL_TEXT variable requirement.

Maybe

I need to finally read the DB2/400 SQL redbook from cover to cover.

Mind shedding some light on the dynamic SQL requirement for me?

Thanks.

Ryan

"Elvis Budimlic"
<ebudimlic@xxxxxxxxxxxxxxxxxxxxxxxxx>
wrote in message news:00e901c76a76$285f5510$6e01a8c0@xxxxxxxx

I don't see why it wouldn't work.  I just tried this in Navigator's
RunSqlScript window:

CREATE PROCEDURE ctetest
RESULT SETS 1
LANGUAGE SQL
BEGIN
 DECLARE SQL_TEXT VARCHAR(100);
 DECLARE ctecursor CURSOR FOR SQL_STATEMENT;

 SET SQL_TEXT = 'WITH T1 AS (SELECT ''Testing'' FROM SYSIBM/SYSDUMMY1)
SELECT * FROM T1';
PREPARE SQL_STATEMENT FROM SQL_TEXT;
OPEN ctecursor;

END;

CALL ctetest;


It worked as I expected it to, returning 'Testing' string.

Elvis

Celebrating 10-Years of SQL Performance Excellence

-----Original Message-----
From: midrange-l-bounces@xxxxxxxxxxxx
[mailto:midrange-l-bounces@xxxxxxxxxxxx] On
Behalf Of Ryan Hunt
Sent: Monday, March 19, 2007 4:47 PM
To: midrange-l@xxxxxxxxxxxx
Subject: CTE in PROC?

I have been trying to use a CTE in a stored proc but can't seem to get

my

syntax right.  I've checked the SQL redbook and the stored proc redbook
(and

of course google) but I haven't seen an example of where to put it.

I've tried:
CREATE PROCEDRE...
blah
blah

BEGIN

DECLARE CURSOR C1 WITH RETURN FOR

WITH Q1 (col1, col2)
AS
(SELECT....)

SELECT blah, blah
FROM Q1

OPEN C1
CLOSE C1
END

Anyway, I can't seem to put the CTE in there (anyware) without causing

an

error.

Can CTE's be used on DB2/400 V5R4?



-- 
This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing
list
To post a message email:
MIDRANGE-L@xxxxxxxxxxxx
To subscribe, unsubscribe, or change list options,
visit: http://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-request@xxxxxxxxxxxx
Before posting, please take a moment to review the archives
at http://archive.midrange.com/midrange-l.





-- 
This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing
list
To post a message email:
MIDRANGE-L@xxxxxxxxxxxx
To subscribe, unsubscribe, or change list options,
visit: http://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-request@xxxxxxxxxxxx
Before posting, please take a moment to review the archives
at http://archive.midrange.com/midrange-l.

-- 
This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing

list

To post a message email:

MIDRANGE-L@xxxxxxxxxxxx

To subscribe, unsubscribe, or change list options,
visit: http://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-request@xxxxxxxxxxxx
Before posting, please take a moment to review the archives
at http://archive.midrange.com/midrange-l.





-- 
This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing

list

To post a message email:

MIDRANGE-L@xxxxxxxxxxxx

To subscribe, unsubscribe, or change list options,
visit: http://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-request@xxxxxxxxxxxx
Before posting, please take a moment to review the archives
at http://archive.midrange.com/midrange-l.