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.
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.
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.
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.
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.
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.
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.