November 3, 2015 at 2:54 am
Hi
I have a stored procedure in which we are deriving some flags. So, we used series of CASE statements.
For examples
CASE
WHEN LEFT(CommissionerCode, 3) IN ('ABC','DEF',...) THEN 1
WHEN PracticeCode IN (.......) THEN 1
WHEN (CommissionerCode IN (.....) OR PracticeCode NOT IN (.....) OR .....) THEN 1
ELSE 0
END
I need to put these conditions in config table and generate dynamic sql.
What is the best way to do this? especially, 3rd condition with OR logic with multiple columns involved.
November 3, 2015 at 3:01 am
I'm not sure I'm following you.
Can you post a more articulate example?
What kind of dynamic sql are you trying to generate?
-- Gianluca Sartori
November 3, 2015 at 1:00 pm
How about..
Create a new table, and let's call it [Codes] for example.
Two columns, names are examples:
[FldNm] [varchar](2) not null
[FldCd] [varchar](3) not null
Population in [Codes] table is:
[FldNm] , [FldCd]
CC , ABC
CC , DEF
PC , 123
PC , 456
Then your case statement would be:
CASE
WHEN LEFT(CommissionerCode, 3) IN (select [FldCd] from [Codes] where [FldNm] = 'CC') THEN 1
WHEN PracticeCode IN (select [FldCd] from [Codes] where [FldNm] = 'PC') THEN 1
WHEN (CommissionerCode IN (select [FldCd] from [Codes] where [FldNm] = 'CC') OR PracticeCode NOT IN (select [FldCd] from [Codes] where [FldNm] = 'PC') OR .....) THEN 1
ELSE 0
END
This way, your sql code could remain unchanged, and all you would have to do is add/remove entries in your [Codes] table, which then would populate your IN criteria.
Solve your problem?
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply