SQL Server WHERE Column Value IN Array

How to pass a list of values to a Stored Procedure for use with the WHERE Column IN ( val_1, val_2, val_3)

SQL Server, Data, SQL UDF

Lets say you know some values that are to be used to match rows from a table and want to retrieve them all via the result of a Stored only executed once, here's how.

First of - How do I use IN?

It's simple!

Take a simple SELECT where you want to match a single value :

SELECT A.* 
FROM A 
WHERE A.ID = 1;

To match Both 1 and 2 you could write :

SELECT A.* 
FROM A 
WHERE A.ID = 1 OR 
      A.ID = 2;

Easy eh? But what if you don't know how many possible matches there are, let alone how much effort it will be to maintain these sorts of WHERE clauses.

IN to the Rescue

Replacing the clause = 1 OR A.ID = 2 with IN( 1, 2 ) will execute and match the same rows.

Giving us :

SELECT A.* 
FROM A 
WHERE A.ID IN( 1, 2 );

Using IN with a Stored Procedure Param

Okay, so it's clear that IN receives an array/collection/list of values. It is possible to write a Dynamic T-SQL statement and execute that via an SP, but I'm going to use a different approach, a User Defined Table-Valued Function / UDF.

I'm going to pass a delimited string to the SP with the Ids I'm interested in for example : "1,2". The UDF will process the string & return a table. Then I can use the values from this table as the params for the IN clause.

A User Defined Table-Valued Function

Using a UDF, this string is split & a table is returned with the values in. This is the UDF StringToBigInt, it should be simple enough to follow my comments.

CREATE FUNCTION  [dbo].[StringToBigInt]
(
    @vals varchar(8000),    -- String to be split
    @delim varchar(5) = ',' -- Delimeter to be used
)

RETURNS @RtnValue TABLE
(
    ID INT IDENTITY(1,1),
    VALUE bigint
)

AS
BEGIN

-- Helper vars
DECLARE @idx int;
DECLARE @val bigint;

-- Start looping of the indexs of delimiter
WHILE (CHARINDEX(@delim,@vals)>0) BEGIN

    -- Find the Index of the delimiter
    SET @idx = CHARINDEX(@delim,@vals);

    -- Extract the value portion 
    SET @val = SUBSTRING( @vals, 1, @idx - 1 )

    -- For each item insert a row
    INSERT INTO @RtnValue ( VALUE )

    -- Set the VALUE column to value extracted
    SELECT VALUE = LTRIM( RTRIM( @val ) )  

    -- Slice the value from the param for next itteration
    SET @vals = SUBSTRING( @vals, @idx + LEN( @delim ), LEN( @vals ) )

END

-- Append another row 
INSERT INTO @RtnValue (VALUE)

-- For last value
SELECT VALUE = LTRIM( RTRIM( @vals ) )

-- Done, return the table of values
RETURN

END 

You can test it using :

SELECT * FROM [StringToBigInt]('1,2,3', ',')

Resulting in :

ID          VALUE
----------- --------------------
1           1
2           2
3           3

(3 row(s) affected)

A Tad Simplified & Lacking Validation

The function is quite simplified. I've excluded checks suchas ISNUMERIC and explicit CAST calls, mainly to make it easer to understand the steps.

The UDF above assumes you pass a string in the expected format; bigint[DELIMITER]bigint if you're using other values or there's a slight chance that the delimited values include some non-numeric value you aught concider adding some validation.

I would suggest using another variable say @rawVal of type varchar/nvarchar. Assign the value to prior to assigning @val. Validate against this using ISNUMERIC, and use CAST when setting the value of @val.

For Example, the body of the WHILE / BEGIN above could be :

-- Find the Index of the delimiter
SET @idx = CHARINDEX(@delim,@vals);

-- Extract the value portion 
SET @rawVal = SUBSTRING( @vals, 1, @idx - 1 )

-- Test that it is a numeric value
IF ISNUMERIC(@rawVal) BEGIN

    -- Cast value to bigint
    SET @val = CAST(@rawValue AS bigint)

    -- All set for insertion
    INSERT INTO @RtnValue ( VALUE )

    // Omitted for brevity

END

Remember of you do this, you need to apply the same logic & Validation for the final item in the string of delimited values.

Using the UDF StringToBigInt in a Stored Procedure

Hooking this up to a Stored Procedure is simple, add another param, of type varchar(8000) that will contain the string of delimited values to match, say @ids.

Then you can use IN with the param @ids & the UDF in-place of any regular WHERE clause, for example:

SELECT A.* 
FROM A 
WHERE A.ID IN(
    SELECT VALUE 
    FROM StringToBigInt(@ids, ',')
);

While there'll be faster methods this is very flexiable; flexibility is something I've always found missing when from SQL, especially Microsoft's SQL Server.