RE: CTE in PROC?

["Elvis Budimlic" <ebudimlic@xxxxxxxxxxxxxxxxxxxxxxxxx>]

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?