Tuesday, December 22, 2015

SQL - Build a comma separated list of string values which can be used with an 'IN' statement

This is what I used to take a list of values and create one string to use with an 'IN' statement.

 Declare @Val1 Varchar(20) = 'V1'
 Declare @Val2 Varchar(20) = NULL
 Declare @Val3 Varchar(20) = 'V3'
 Declare @Val4 Varchar(20) = 'V4'


 Declare @Val_List Varchar(8000)
 Declare @Select_SQL Varchar(8000)

 Set @Val_List =
Case When @Val1 IS NULL Then '' Else + '''' + @Val1 + '''' End  
    + Case When @Val2 IS NULL Then '' Else + ',' + '''' + @Val2 + '''' End  
    + Case When @Val3 IS NULL Then '' Else + ',' + '''' + @Val3 + '''' End  
    + Case When @Val4 IS NULL Then '' Else + ',' + '''' + @Val4 + '''' End;
   
   
 Set @Select_SQL = 'Select * From Table_Name Where Val In (' + @Val_List + ')';

 Print @Select_SQL;

-- This is the result:

Select * From Table_Name Where Val In ('V1','V3','V4')

No comments:

Post a Comment