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.