I am trying to convert dynamic sql into static sql due to permission issue, I faced a challange when i am trying to convert this part of code given below
-- join to ADDR_DETAIL
SET @join_sql = 'inner join ADDR_DETAIL on ADDR_DETAIL.GRP_CONTACT_ID = pm.GRP_CONTACT_ID AND '
IF (@ChannelEntity = '') OR (@ChannelEntity IS NULL)
-- Preferred Address
SET @join_sql = @join_sql + 'ADDR_DETAIL.PREFERRED_ADDR_FL=''Y'' '
-- Use specified Channel Enitity
SET @join_sql = @join_sql + @ActChannelEntity + ' = ''' + @ChannelEntity + ''' '
IF (@Channel = 'E')
SET @filter_sql = @filter_sql + ' AND RTRIM(ADDR_DETAIL.email_addr) <> '''' '
-- Address Cleansing
IF (@ACleanse = 'CL')
-- Only cleansed addresses
SET @join_sql = @join_sql + ' AND ADDR_DETAIL.CLNS_STATUS LIKE ''C%'' '
ELSE IF (@ACleanse = 'AG')
-- All addresses except 'Gone Aways'
SET @join_sql = @join_sql + ' AND ADDR_DETAIL.CLNS_STATUS != ''GA'' '
I tried to use the case statement something like this given below inside Join but it has not worked, I know this is very far away from my solution, but please help me to get the exact solution.
INNER JOIN ADDR_DETAIL (NOLOCK) ON
ADDR_DETAIL.GRP_CONTACT_ID = part_master.GRP_CONTACT_ID AND
WHEN @ChannelEntity = '' OR @ChannelEntity IS NULL THEN
ADDR_DETAIL.PREFERRED_ADDR_FL END ='Y' AND
WHEN @ChannelEntity <> '' OR @ChannelEntity IS NOT NULL THEN
ADDR_DETAIL.ADDR_TYP_CD END = @ChannelEntity AND
CASE WHEN @ACleanse = 'CL' THEN
ADDR_DETAIL.CLNS_STATUS END like 'C%' AND
CASE WHEN @ACleanse = 'AG' THEN
ADDR_DETAIL.CLNS_STATUS END != 'GA' AND
RTRIM(ADDR_DETAIL.email_addr) <> ''
I am not very good in SQL please help me to solve this problem