RE: CTE in PROC?

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.