Local variable with multiple value list

Question

I use Excel connection to connect to SQL Server to query data from SQL server to Excel.

I have below WHERE clause in the Excel connection couple times. I need to replace the WHERE multiple value list from time to time. To simply the replacement, I want to use a local parameter, @Trans. With the local parameter, I can change it only and all SQL will use it to query.

WHERE Type in ('R','D','C')

If it is single option, below code works.

DECLARE @TRans CHAR(200)= 'R';
SELECT .....
WHERE Type in (@Trans)

If it is multiple options, the below code does not works

DECLARE @TRans CHAR(200)= 'R,D,C';
SELECT .....
WHERE Type in (@Trans)

DECLARE @TRans CHAR(200)= '''R'''+','+'''D'''+','+'''C''';
SELECT .....
WHERE Type in (@Trans)

How to declare @Trans for multiple value list, for example ('R','D','C')? Thank you.

Answer 1

You can use dynamic sql

DECLARE @TRans VARCHAR(200)= '''R'',''D'',''C''';
DECLARE @sql VARCHAR(MAX) = '';

SET @sql = 'SELECT * FROM table WHERE Type in (' + @Trans + ');'

EXEC @sql

Take note of the quotes for the values in @TRans since these character values.

If you want to check the value of @sql which you will see the constructed sql statement, replace EXEC @sql with PRINT @sql.

Result of @sql

SELECT * FROM table WHERE Type in ('R','D','C');

Answer 2

As you can see by now, SQL Server does NOT support macro substition. This leaves a couple of options. One is to split the string.

If not 2016, here is a quick in-line approach which does not require a Table-Valued Function

Example

Declare @Trans varchar(max)='R,D,C'  -- Notice no single quotes

Select ...
 Where Type in (
                Select RetVal = LTrim(RTrim(B.i.value('(./text())[1]', 'varchar(max)')))
                From  (Select x = Cast('<x>' + replace(@Trans,',','</x><x>')+'</x>' as xml).query('.')) as A 
                Cross Apply x.nodes('x') AS B(i)
               )