Wednesday, September 22, 2010

Handling comma separated values parameter in a stored procedure without using inline sql

I have seen lots of implementation for comma separated values parameters, all using inline SQL to construct the query.
for example:
Suppose the parameter is @param having the value '1,2,3'
and we are selecting rows with state in (1,2,3)

Then the query would look like this

query='select * from TableName where state in (' + @param + ')'
exec query

Since i don't like inline SQL, i was looking for another methods

I found this method somewhere, i don't remember where exactly. It is based on constructing a table, and inserting the comma separated values in this  table, then in the query either we can inner join with this table to get the desired values only or we can use where clause

example on constructing this temp table

declare @param nvarchar(50)
set @param = N'1,2,3,4'
(SELECT * FROM tempdb.dbo.sysobjects WHERE ID = OBJECT_ID(N'tempdb..#temptable'))
DROP TABLE #temptable

CREATE TABLE #temptable(Code int)

   DECLARE @code varchar(10), @Pos int

    SET @param = LTRIM(RTRIM(@param))+ ','
    SET @Pos = CHARINDEX(',', @param, 1)

    IF REPLACE(@param, ',', '') <> ''
        WHILE @Pos > 0
                SET @code = LTRIM(RTRIM(LEFT(@param, @Pos - 1)))
                IF @code <> ''
                        INSERT INTO #temptable (code)
                        VALUES (CAST(@code AS int)) --Use Appropriate conversion
                SET @param = RIGHT(@param, LEN(@param) - @Pos)
                SET @Pos = CHARINDEX(',', @param, 1)

--select * FROM #temptable

Next in the main query, it can be like this

Select * FROM TableName
INNER JOIN #temptable on TableName.State = #temptable.code


Select * FROM TableName
where state in (select code from #temptable)