Using the IN Clause With Stored Procedures

Recently I have been working on creating a dynamic reporting engine for use within the DotNetNuke system, a method to allow for the execution of a stored procedure and the proper display of the results. This includes creating a list of parameters, including dynamically loaded lookup systems and more. Well the most complex item that I have had to condition for was how to deal with filter options that require the use of the SQL IN clause, there are many ways of doing this, but I have found a way that appears to work perfectly for my needs, this will be explained in this posting.

Disclaimer

This solution is provided as an example, and may not work 100% to meet your specific business needs, careful consideration must be taken when working with the code provided below as due to its dynamic nature SQL injection items need to be considered when investigating the input data provided to the stored procedure. If you have questions about the limitations or security surrounding this sample, please e-mail me or make a post to the forums on this site.

Pre-Requisites

This example assumes that you already have a comma-separated string that contains integer values that should be used as part of a list to be used inside of an "IN" clause. For example, if you have a query that looks like the following.

SELECT *
FROM Reports
WHERE ReportId IN (@Reports)

This will NOT work in a stored procedure. The most common solution is to use dynamic SQL, which I personally do not like using. My solution will modify this to use a custom created Function that will parse the values into a table, then use that table to get the listing.

The Solution

As I mentioned above, the solution is very simple, create a function that converts the data into a table. Below is the needed procedure, please see the rest of the article for important notes.

CREATE FUNCTION dbo.funcListToTableInt(@list as varchar(8000), @delim as varchar(10))
RETURNS @listTable table(
  Value INT
  )
AS
BEGIN
    --Declare helper to identify the position of the delim
    DECLARE @DelimPosition INT
    
    --Prime the loop, with an initial check for the delim
    SET @DelimPosition = CHARINDEX(@delim, @list)

    --Loop through, until we no longer find the delimiter
    WHILE @DelimPosition > 0
    BEGIN
        --Add the item to the table
        INSERT INTO @listTable(Value)
            VALUES(CAST(RTRIM(LEFT(@list, @DelimPosition - 1)) AS INT))
    
        --Remove the entry from the List
        SET @list = right(@list, len(@list) - @DelimPosition)

        --Perform position comparison
        SET @DelimPosition = CHARINDEX(@delim, @list)
    END

    --If we still have an entry, add it to the list
    IF len(@list) > 0
        insert into @listTable(Value)
        values(CAST(RTRIM(@list) AS INT))

  RETURN
END
GO

The above code creates a function that will process our input, converting each value to an integer and returning a single-column table. The procedure created is called, funcListToTableInt. Now, with a simple modification to the sample query we are able to successfully run the query as part of a stored procedure.

SELECT *
FROM Reports
WHERE ReportId IN (
		    SELECT Value
		    FROM funcListToTableInt(@Reports,',')
                   )

Conclusion

This method provides a simple manner to convert a comma-separated listing of values to something that can be used in an IN clause within SQL Server Stored procedures. Two parameters are passed to the function, allowing other delimiters to be used if needed. One could also use this for IN's of different data types, but a new function would be needed. I hope that this has helped someone solve a dynamic query situation. As always please provide any feedback below and any questions to the forum.