|
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@xxxxxxxxI 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.
As an Amazon Associate we earn from qualifying purchases.
This mailing list archive is Copyright 1997-2024 by midrange.com and David Gibbs as a compilation work. Use of the archive is restricted to research of a business or technical nature. Any other uses are prohibited. Full details are available on our policy page. If you have questions about this, please contact [javascript protected email address].
Operating expenses for this site are earned using the Amazon Associate program and Google Adsense.