Define variable to use with IN operator (T-SQL)

ENHANCE: Using table variables

Basic Table Variable Setup

Begin by initializing a table variable with a data type that matches your query column:

DECLARE @Colors TABLE (ColorName NVARCHAR(50)); -- We love colors 🎨
INSERT INTO @Colors VALUES ('Red'), ('Green'), ('Blue'); -- Populating your fav colors


Utilizing IN with Table Variables

Next, use the table variable in a subquery to streamline your query results:

SELECT * FROM Products WHERE Color IN (SELECT ColorName FROM @Colors);


Handling Dynamic Lists

When life gives you dynamic lists, STRING_SPLIT function (available in SQL Server 2016 or newer) turns them into tasty lemonade:

DECLARE @ColorList NVARCHAR(MAX) = 'Red,Green,Blue';
DECLARE @Colors TABLE (ColorName NVARCHAR(50));

INSERT INTO @Colors (ColorName)
SELECT VALUE FROM STRING_SPLIT(@ColorList, ',');


Quick Lists Using UNION ALL

Alternatively, cook up a quick list right in your SELECT statement using UNION ALL when you're juggling ad-hoc values:

SELECT * FROM Products WHERE Color IN (
    SELECT 'Red'
    UNION ALL SELECT 'Green'
    UNION ALL SELECT 'Blue'
);


EXPLORE: Higher-Level Techniques & Potential Pitfalls

Embracing the Unknown with sp_executesql

For cases where you don't know the list of values at compile time...

DECLARE @SQL NVARCHAR(MAX), @Params NVARCHAR(MAX), @ValueList NVARCHAR(MAX) = '1,2,3';
SET @SQL = N'SELECT * FROM MyTable WHERE ID IN (SELECT VALUE FROM STRING_SPLIT(@pValueList, '',''))';
SET @Params = N'@pValueList NVARCHAR(MAX)';
EXEC sp_executesql @SQL, @Params, @pValueList = @ValueList;


Flexibility level: ninja! 🥷

Avoiding Data Type Pitfalls

Ensure variable data type and the query column data type are identical twins.

-- For example:
DECLARE @ID INT = 123;
SELECT * FROM MyTable WHERE ID = @ID; -- Works great

DECLARE @VarcharID VARCHAR(10) = '123';
SELECT * FROM MyTable WHERE ID = @VarcharID; -- Possible unexpected results 🐰🎩


Performance: Table Variables vs Temporary Tables

Table variables are quick sprinters, but for marathons (large datasets), consider temporary tables.

No More Dirty Laundry with CTEs

Keep your code tidy with Common Table Expressions (CTE)...

WITH ValueList AS (
    SELECT 'Red' AS ColorName
    UNION ALL SELECT 'Green'
    UNION ALL SELECT 'Blue'
)
SELECT * FROM Products WHERE Color IN (SELECT ColorName FROM ValueList);


Now, isn't that code as clean as a freshly-ironed shirt? 😎