How to create a variable to use the IN clause SELECT query SQL? | ecode10.com

How to create a variable to use the IN clause SELECT query SQL?

Database code part

DATABASE

Wednesday, November 19, 2025

Views: 22 SHARE

To use a variable in an IN clause within a SELECT query in SQL Server, you typically need to handle the variable as a set of values rather than a single string. This can be achieved using a table variable or a temporary table, or by parsing a delimited string variable into a table.

Using a Table Variable:

This method is suitable when you have a list of values to store in a variable.

DECLARE @MyIDs TABLE (ID INT);

INSERT INTO @MyIDs (ID)
VALUES (1), (5), (10), (15);

SELECT *
FROM YourTable
WHERE ColumnID IN (SELECT ID FROM @MyIDs);

Using a Temporary Table:

Similar to a table variable, but temporary tables are visible across the entire session and can be more flexible for larger datasets or more complex operations.

CREATE TABLE #TempIDs (ID INT);

INSERT INTO #TempIDs (ID)
VALUES (2), (8), (12);

SELECT *
FROM YourTable
WHERE ColumnID IN (SELECT ID FROM #TempIDs);

DROP TABLE #TempIDs; -- Clean up the temporary table

Parsing a Delimited String Variable (for comma-separated lists):

If your variable holds a comma-separated string of values, you need to parse it into a table format. This often involves a user-defined function or XML methods.

-- Example using a common string splitting function (you would need to create this function)
-- CREATE FUNCTION dbo.SplitString (@InputString NVARCHAR(MAX), @Delimiter CHAR(1))
-- RETURNS @OutputTable TABLE (Value NVARCHAR(MAX))
-- AS
-- BEGIN
--     -- Implementation of string splitting logic
--     RETURN
-- END;

DECLARE @IDList NVARCHAR(MAX) = '3,7,11';

SELECT *
FROM YourTable
WHERE ColumnID IN (SELECT Value FROM dbo.SplitString(@IDList, ','));

Note: Direct use of a string variable containing comma-separated values within an IN clause (e.g., WHERE ColumnID IN (@IDList)) will not work as expected, as SQL Server will treat the entire string as a single value. Therefore, parsing the string into a table format is crucial for this scenario.