Stored Procedure Fetch first parameter dont work

ZeroQool · #1 (**permalink**) 08-14-10, 12:57

hello, i would like to use a parameter to control the fetch first rows. But the db2 system doesnt accept the command.

Code:

	CREATE PROCEDURE MOVEPGM.GETLIST(
IN PMAXRESULTS INTEGER
	) 
RESULT SETS 1                                
LANGUAGE SQL                                 
BEGIN                                        
DECLARE c1 CURSOR WITH RETURN FOR            
select * from table1 FETCH FIRST PMAXRESULTS ROWS ONLY; 
OPEN C1;
END ;

thanks

n_i · #2 (**permalink**) 08-14-10, 13:04

And the question is?

ZeroQool · #3 (**permalink**) 08-14-10, 13:07

Quote:

Originally Posted by n_i

And the question is?

the create sp command doesnt work

exception: "PMAXRESULTS" isnt a valid parm

Quote:

SQL-Status: 42601
Vendorencode: -104
Nachricht: [SQL0104] Token PMAXRESULTS ungültig. Gültige Token: ROW ROWS. Ursache . . . . : Bei Token PMAXRESULTS wurde ein Syntaxfehler entdeckt. Token PMAXRESULTS ist kein gültiges Token. ROW ROWS ist eine Teilliste gültiger Token. Diese Liste geht davon aus, dass die Anweisung bis zu diesem Token korrekt ist. Es kann eine frühere Stelle in der Anweisung fehlerhaft sein, aber bis zu dieser Stelle scheint die Syntax gültig zu sein. Fehlerbeseitigung: Einen der folgenden Schritte durchführen und die Anforderung wiederholen: - SQL-Anweisung im Bereich des Token PMAXRESULTS überprüfen. Anweisung korrigieren. Der Fehler kann ein fehlendes Komma oder Anführungszeichen sein, er kann in einem falsch geschriebenen Wort oder in der Anordnung der Klauseln begründet sein. - Ist das fehlerhafte Token <ANWEISUNGSENDE>, die Anweisung korrigieren, da sie mit einer ungültigen Klausel endet.

Marcus_A · #4 (**permalink**) 08-14-10, 13:55

The fetch first clause cannot contain a variable when using static (embedded) SQL. You could create the entire cursor with dynamic SQL in order to use a variable.

ZeroQool · #5 (**permalink**) 08-14-10, 14:25

Quote:

Originally Posted by Marcus_A

The fetch first clause cannot contain a variable when using static (embedded) SQL. You could create the entire cursor with dynamic SQL in order to use a variable.

thanks! exist a performance different between dynamic and static?

Marcus_A · #6 (**permalink**) 08-14-10, 15:26

Quote:

Originally Posted by ZeroQool

thanks! exist a performance different between dynamic and static?

There is some performance difference because you have to prepare the statement and then execute it. Also, I beleive that you will need to make all the other statements (open, fetch, etc) that refer to the cursor also be dynamic (unless there is some enhancement in a recent release of DB2 that I don't know about).

However, since most dynamic statements should already be in package cache from previous executions (if they are identical), a dynamic statement may not need to be re-compiled (to determine access plan) each time it is executed. Static (embedded) SQL statements are always compiled ahead of time when the SP is created (there is a package that is created along with the SP that contains the compiled SQL).

Overall, static SQL is usually faster, but the difference may not be significant. You should do testing to determine the difference if the transaction is critical.