Temp Tables and Passing a Delimited Field as a Parameter

by William Ryan

Everyone knows you should use Stored Procedures instead of dynamic SQL.  This debate has raged on and on in the various newsgroups and the verdict is clear, USE STORED PROCEDURES.  But if you don't care about Security, Performance, Maintainability or Scalability and still shun Stored Procedures, at least use  Parameters.

That's old hat right?  Well, there are some times Dynamic SQL has its uses (although they are quite limited).  One of the more frequent things I've come across is people shunning Stored Procs because you can't pass a Delimmited Parameter into your proc and use it in and IN Clause.  So if you passed in a value that was something like this,

SqlCommand cmd = new SqlCommand(); cmd.Parameters.Add("@DelimitedString", "1,2,3,4,5,6");

and you tried using it as a value in a query using IN:

SELECT * FROM      myTable WHERE         Department IN(@DelimetedString)

You wouldn't get any results, at least not the ones you expected.  You see, part of what ADO.NET does for you is escape your strings (that's why they are so good against Injection Attacks) So what do you do? There are a few ways to go about it, but basically the approach is the same:  1)  Parse the string into individual values 2)  Query against them.  To accomplish this, I decided to use a Temporary tables.  Temporary Tables do many things for you, the main one is that they go away without your intervention, so they make a great place to hold things, well, temporarily.

So, I looped through the string, inserting each token into the temp table.  Once I got it parsed, I simply fire my SELECT statement which uses a Subquery and an IN.  SQL Server can run this very quickly and it can create and drop Temporary tables very quickly, after all, that's what they were designed for.  So, by using a little T-SQL and a Temp table, pulling this off is quite simple.  Now, for the sake of illustration I've just used T-SQL, but you can simply wrap Create Procedure WhateverName and this code (which I at the very end), using @Token as a variable which will be passed from client code, and your off to the races!

DECLARE @Token AS VARCHAR(50) DECLARE @Temp  AS VARCHAR(50) DECLARE @In INT CREATE TABLE #HOLDER(FirstVal VARCHAR(50)) WHILE LEN(@Token) > 0     BEGIN        IF CHARINDEX(',', @Token) =0           BEGIN              SET @TEMP =@Token              SET @TOken = ''              INSERT INTO #Holder (FirstVal) VALUES(@Temp)           END        ELSE           BEGIN              SET @Temp = LEFT(@Token, CHARINDEX(',', @Token)-1)                    INSERT INTO #Holder (FirstVal) VALUES(@Temp)              SET @Token = RIGHT(@Token, LEN(@Token)-LEN(@Temp)-1)        END     END SELECT * FROM BlahBLahBLah WHERE SomeField IN (SELECT FirstVal FROM #Holder)

OR SqlCommand cmd = new SqlCommand(); cmd.Parameters.Add("@Token", "1,2,3,4,5,6"); cmd.CommandType = CommandType.StoredProcedure; dr = cmd.ExecuteReader(); CREATE PROCEDURE usp_MyParserForIN      @Token AS VARCHAR(50)--or whatever length you'll need AS DECLARE @Temp  AS VARCHAR(50) CREATE TABLE #HOLDER(FirstVal VARCHAR(50)) WHILE LEN(@Token) > 0     BEGIN        IF CHARINDEX(',', @Token) =0           BEGIN              SET @TEMP =@Token              SET @TOken = ''              INSERT INTO #Holder (FirstVal) VALUES(@Temp)           END        ELSE           BEGIN              SET @Temp = LEFT(@Token, CHARINDEX(',', @Token)-1)                    INSERT INTO #Holder (FirstVal) VALUES(@Temp)              SET @Token = RIGHT(@Token, LEN(@Token)-LEN(@Temp)-1)        END     END SELECT * FROM BlahBLahBLah WHERE SomeField IN (SELECT FirstVal FROM #Holder)