title: DB2 Stored Procedure: Dynamically Building a Select Statement for cursor
body: <p>I am fairly new to stored procedures. I naively thought that I could build up a select statement as follows. I cannot, and some of you will grin with what I came up with.</p>
  
  <p>How does one do what I am trying to do though? </p>
  
  <p>Thanks in advance.</p>
  
  <pre><code>CREATE PROCEDURE GET_CUSTOMER_FOR_BORROWER_LETTER (
  
  
      IN APPLICATION_ID INTEGER,
      IN GET_GUARANTOR INTEGER,
      IN GET_PREFERRED_CONTACT INTEGER
      )
  
  DYNAMIC RESULT SETS 1
  READS SQL DATA
  
  P1:BEGIN
      DECLARE selectStmt VARCHAR(800);
      DECLARE selectStmtPreferred VARCHAR(400);
      DECLARE selectStmtApplicants VARCHAR(400);
      DECLARE selectStmtGuarantor VARCHAR(400);
  
  
      DECLARE cursor1 CURSOR WITH RETURN FOR
      selectStmt -- will define this later, conditionally (babe in the woods :) )
      OPEN cursor1;
  
  
      set selectStmtPreferred = 'select "preferred applicant" as recipient_type, app.APPLICATION_ID, cust.KEY from application app, customer cust, application_detail appd where app.application_id = 407634 and app.APPLICATION_ID = appd.APPLICATION_ID  and appd.PREFERRED_CONTACT_ID = cust.KEY';
  
      set selectStmtApplicants = 'select "applicant" as recipient_type, app.APPLICATION_ID, cust.KEY from application app, applicant applc, customer cust where app.application_id = 407634 and applc.APPLICATION_ID = app.APPLICATION_ID and applc.CUST_ID = cust.CUST_ID';
  
      set selectStmtGuarantor = ' union select "guarantor" as recipient_type ,app.APPLICATION_ID, cust.KEY from application app, application_guarantor appg, customer cust where app.application_id = 407634 and appg.APPLICATION_ID = app.APPLICATION_ID and appg.CUST_ID = cust.CUST_ID';
  
      IF GET_PREFERRED_CONTACT = 1 THEN
  
          IF GET_GUARANTOR = 1 THEN 
              SET selectStmt = concat (selectStmtPreferred,selectStmtGuarantor);
          ELSE 
              SET selectStmt = selectStmtPreferred;
          END IF;
      ELSE 
          IF GET_GUARANTOR = 1 THEN 
              SET selectStmt = concat (selectStmtApplicants,selectStmtGuarantor);
          ELSE 
              SET selectStmt = selectStmtApplicants;
          END IF;
      END IF;
  selectStmt = concat (selectStmtPreferred,";");
  
  
  END P1@
  </code></pre>