News

Copyright © 2008-2019 Paula DiTallo

Tag Cloud



Does Dynamic SQL work in DB2?

Yes.

Here's an example--in this case, if the incoming parameter is empty, you won't need the WHERE clause, instead you will issue the ORDER BY for every row in the table. If the incoming parameter has a value, then search only for that employee identifier, no need for an ORDER BY clause.

CREATE PROCEDURE MYPROC01(IN @EMPLID VARCHAR(20))

LANGUAGE SQL
READS
SQL DATA
RESULT SETS 1

S1 : BEGIN

DECLARE STMT VARCHAR(1000) ;
DECLARE COND_1 VARCHAR(50);

DECLARE CURS1 CURSOR FOR S1

 IF @EMPLID <> ' '
   THEN
     SET COND_1 = '  WHERE emplid = @EMPLID'
 ELSE
     SET COND_1 = '  ORDER BY emplid'

SET STMT = 'SELECT emplid,
           company,
           firstname,
          lastname,
           location
     FROM MYHR.EMPL '

 -- build statement dynamically
  SET STMT = STMT || COND_1;

PREPARE STMT FROM CURS1 ;

OPEN CURS1 ;
RETURN;

CLOSE CURS1;


END S1

Wednesday, October 26, 2011 4:26 PM

Feedback

No comments posted yet.


Post A Comment
Title:
Name:
Email:
Comment:
Verification: