Question

Using Case Statement Inside SQL Join

SQL, T-SQL, JOIN, CASE statement

Hi,
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)
     BEGIN
       -- Preferred Address
       SET @join_sql = @join_sql + 'ADDR_DETAIL.PREFERRED_ADDR_FL=''Y'' '
     END
     ELSE
     BEGIN
       -- Use specified Channel Enitity
       SET @join_sql = @join_sql + @ActChannelEntity + ' = ''' + @ChannelEntity + ''' '
     END
 
     -- Email
     IF (@Channel = 'E')
       SET @filter_sql = @filter_sql + ' AND RTRIM(ADDR_DETAIL.email_addr) <> '''' '
 
 
     -- Address Cleansing
     IF (@ACleanse = 'CL')
     BEGIN
 	-- Only cleansed addresses
 	SET @join_sql = @join_sql + ' AND ADDR_DETAIL.CLNS_STATUS LIKE ''C%'' '
     END
     ELSE IF (@ACleanse = 'AG')
     BEGIN
 	-- All addresses except 'Gone Aways'
 	SET @join_sql = @join_sql + ' AND ADDR_DETAIL.CLNS_STATUS != ''GA'' '
     END
 

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
 				CASE  
 					WHEN @ChannelEntity = '' OR @ChannelEntity IS NULL THEN
 						ADDR_DETAIL.PREFERRED_ADDR_FL END ='Y' AND
 				CASE
 					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

You will have to build the whole SQL statement into a variable in one go then execute it.

The INNER JOIN is implied

e.g. select x.a from x, y where x.rowid=y.rowid

This is an inner join and will only return rows where there is a reference in both tables