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 perfect 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 datatypes, 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.
Posted by Mitchel on Friday, August 08, 2008
Name (required)
Email (required)
Website