Passing Parameters to Dynamic SQL

[krotha]

I am trying to pass the values from the parameters to a dynamic sql and I get syntax error.I have many parameters like this and need to build a query depends on parameters passed, any help is appreciated.

CREATE PROCEDURE Test.Search ( IN P_number varchar (50))
DYNAMIC RESULT SETS 1

P1: BEGIN


DECLARE stmt VARCHAR(4000);
declare v_stmt varchar(4000);


DECLARE cursor1 CURSOR WITH RETURN FOR v_stmt;

set stmt =
'SELECT ID,NUMBER FROM Temp WHERE 1=1' ;

if P_number is not null then
set stmt = stmt || ' AND number = P_number';
END if;

prepare v_stmt from stmt;
OPEN cursor1;

END P1

 

[sathyarams]

If number is a text type column then,
set stmt = stmt || ' AND number = ''' || P_number || '''' ;

If number is a numeric column,

set stmt = stmt || ' AND 1 = ' || P_number ;

HTH

Sathyaram


For db2 resoruces visit

http://www.db2click.com

More DB2 questions answered at

http://www.dbforums.com/f8

&

http://www.idug.org/user/UserLogin.asp

 

[krotha]

It worked. Thanks a lot for you help.