DB2 Stored Procedure: Dynamically Building a Select Statement for cursor
24,910
Solution 1
I solved this. It's ugly but it's solved.
P1:BEGIN
DECLARE preferredWithGuarantor CURSOR WITH RETURN FOR
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 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;
DECLARE preferred CURSOR WITH RETURN FOR
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;
DECLARE applicantWithGuarantor CURSOR WITH RETURN FOR
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 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;
DECLARE applicant CURSOR WITH RETURN FOR
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;
IF GET_PREFERRED_CONTACT = 1 THEN
IF GET_GUARANTOR = 1 THEN
open preferredWithGuarantor;
ELSE
open preferred;
END IF;
ELSE
IF GET_GUARANTOR = 1 THEN
open applicantWithGuarantor;
ELSE
open applicant;
END IF;
END IF;
END P1@
Solution 2
Try this out next time you need to build some dynamic SQL statements.
DECLARE SELECT_STATEMENT VARCHAR(8000);
DECLARE cursor1 CURSOR WITH RETURN FOR SQL_STATEMENT;
...build dynamic sql here...
PREPARE SQL_STATEMENT FROM SELECT_STATEMENT;
OPEN cursor1;
To be clear, SQL_STATEMENT is any name you want. Just make sure it's the same in the CURSOR declaration and the PREPARE statement.
Author by
HellishHeat
Full stack developer with 10+ years experience in Financial services, Pharma and Energy.
Updated on July 05, 2020Comments
-
HellishHeat almost 4 years
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.
How does one do what I am trying to do though?
Thanks in advance.
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@