Build a Where Clause in a Stored Procedure and then Use it in a Select Statement (Possible?)

Toad for IBM DB2
1

Is this even possible. I’ve scoured the internet and can’t find an answer.

BEGIN

Declare WhereClause VarChar(4000);

Declare Main VarChar(3500);

Declare SubMain VarChar(500)

BEGIN

Select Prev_Month_St_Dt Into BegOfPrevMon From TMSR_DATA_DATES;

Select Prev_Month_End_Dt Into EndOfPrevMon From TMSR_DATA_DATES;

Select Month(Prev_Month_St_Dt) Into CurMon From TMSR_DATA_DATES;

Select Day(Prev_Month_End_Dt) Into LastDay From TMSR_DATA_DATES;

Set Main = “Yada Yada…” —about 50 lines of codes

Set SubMain to some other based on the select into values…about another 50 lines of code…

Set WhereClause = Main || SubMain;

END;

BEGIN

DECLARE C1 CURSOR WITH RETURN TO CLIENT FOR

Select yada yada yada From – 500 lines of code

Insert the WhereClause here

UNION

Select yada yada yada From – 500 lines of code

Insert the WhereClause here

OPEN C1;

END;

END!

2

Mojoala, have you ever written dynamic SQL before? This seems fairly easy concept with dynamic SQL.

3

I ended up building the query as a string.

doing it dynamically would have made it extremely bulky.

4

I am confused and was wondering if you could show me an example of what you ended up doing. How is building the query as a string and dynamically building the string the same thing?

I have written my where clause as something like this.

Where (@Parm1 is null or @Parm1 = col_1)

and (@Parm2 is null or @Parm2 = col_2)