 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
| HomeDiscussion Groups Database Servers DB2InformixIngresMS SQLOraclePervasive.SQLPostgreSQLProgressSybaseDesktop Databases FileMakerFoxProMS AccessParadoxGeneral General DB TopicsDatabase TheoryRelated Topics Java Development.NET DevelopmentVB DevelopmentMore Topics ... |
Database Forum / DB2 Topics / April 2006set variables to be used in creation of result set.
I was wondering if there is a way to create a stored procedure that I can DECLARE Variables then set these variables using a select statment -- up to here works. then use a different statement to generate a result set. I've been trying to do this with using a Cursor but I get an error that I can't DECLARE a cursor after a SQL statement has been run. I don't need it to be returned in a Cursor if there is another way. I just need a result set. > I was wondering if there is a way to create a stored procedure that I > can [quoted text clipped - 4 lines] > I've been trying to do this with using a Cursor but I get an error that > I can't DECLARE a cursor after a SQL statement has been run. Wrap the DECLARE CURSOR statement in its own BEGIN ... END block. > I don't need it to be returned in a Cursor if there is another way. I > just need a result set. Result sets and cursors are tied to each other.  SignatureKnut Stolze DB2 Information Integration Development IBM Germany thank you. > > I was wondering if there is a way to create a stored procedure that I > > can DECLARE Variables then set these variables using a select statment [quoted text clipped - 5 lines] > > Wrap the DECLARE CURSOR statement in its own BEGIN ... END block. Was this added in v8? I got "...LINE 20 COL 1 NESTED COMPOUND STATEMENTS NOT ALLOWED" as a build error when I tried that, but I'm using v7. SignatureJ. Moreno >>> I was wondering if there is a way to create a stored procedure that I >>> can DECLARE Variables then set these variables using a select statment [quoted text clipped - 9 lines] > I got "...LINE 20 COL 1 NESTED COMPOUND STATEMENTS NOT ALLOWED" > as a build error when I tried that, but I'm using v7. DB2 for zOS presumably V7? Try a dynamic cursor declaration, that should work. Cheers Serge SignatureSerge Rielau DB2 Solutions Development IBM Toronto Lab -snip- > >> Wrap the DECLARE CURSOR statement in its own BEGIN ... END block. > > [quoted text clipped - 4 lines] > > > DB2 for zOS presumably V7? Yep. > Try a dynamic cursor declaration, that should work. A stored procedure with this.... -- start here BEGIN DECLARE cursor1 INSENSITIVE SCROLL CURSOR WITH RETURN FOR SELECT count(aColumn) FROM aTable; -- Cursor left open for client application OPEN cursor1; END: -- end here compiles. Wrapping that in an extra begin/end gets the above error message. SignatureJ. Moreno > -snip- >>>> Wrap the DECLARE CURSOR statement in its own BEGIN ... END block. [quoted text clipped - 22 lines] > compiles. Wrapping that in an extra begin/end gets the above error > message. Well, yes, that's what you said. Here is what I meant: CREATE PROCEDURE p() BEGIN DECLARE var CHAR(1); DECLARE txt VARCHAR(100); DECLARE stmt STATEMENT; DECLARE cur CURSOR WITH RETURN FOR stmt; SET txt = 'SELECT * FROM SYSIBM.SYSDUMMY1 WHERE IBMREQD = ?'; PREPARE stmt FROM txt; SET var = 'Y'; OPEN cur USING var; END @ CALL P() @ Result set 1 -------------- IBMREQD ------- Y 1 record(s) selected. Return Status = 0 Cheers Serge SignatureSerge Rielau DB2 Solutions Development IBM Toronto Lab -snip nested compound statement not building inside stored procedure- > > compiles. Wrapping that in an extra begin/end gets the above error > > message. > > > Well, yes, that's what you said. Here is what I meant: Thanks for the example, I think I understand now. > CREATE PROCEDURE p() > BEGIN [quoted text clipped - 7 lines] > OPEN cur USING var; > END Doesn't build. LINE 14 COL 17 UNSUPPORTED DATA TYPE DISTINCT TYPE ENCOUNTERED IN SQL VARIABLE STMT Off to do some more googling... SignatureJ. Moreno > -snip nested compound statement not building inside stored procedure- >>> compiles. Wrapping that in an extra begin/end gets the above error [quoted text clipped - 20 lines] > > Off to do some more googling... Try removing DECLARE stmt STATEMENT; Cheers Serge SignatureSerge Rielau DB2 Solutions Development IBM Toronto Lab -snip- > >> CREATE PROCEDURE p() > >> BEGIN [quoted text clipped - 15 lines] > Try removing > DECLARE stmt STATEMENT; That works. Thanks, I'll get the hang of this eventually. SignatureJ. Moreno > -snip- >>>> CREATE PROCEDURE p() [quoted text clipped - 17 lines] > > That works. Thanks, I'll get the hang of this eventually. Me too. I just learned why for DB2 for LUW that DECLARE is optional. What we do for compatibility with the elder brother... :-) Cheers Serge SignatureSerge Rielau DB2 Solutions Development IBM Toronto Lab |
Reply to this Message |
 Sign In
 Join
 My Latest Posts My Monitored Threads My Blog My Photo Gallery My Profile My Homepage Start New Thread Enable EMail Alerts Rate this Thread
|
©2008 Advenet LLC Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.