IN with Parameter
-
I often use the IN keyword like this:
WHERE Mycolum IN ('value1', 'value2', 'value3')
Now I need to pass the set of values as a parameter into a stored procedure.
If I write
WHERE MyColumn IN (@values)
and pass in 'value1' as parameter it works. However if I pass in '''value1', 'value2', 'value3''' it does not work! I am totally stuck. Any help is appreciated.
-
Hi! As far as I know, it's not that simple...
The way I get around this problem may not be the best (guys, be at the ready to chip in!!) but I have two possible ways of completing this task.
The first (which I don't like) is to use dynamic SQL (this is why I don't like it
) to build a query string involving your values. For example:DECLARE @sqlStr NVARCHAR(100)
DECLARE @values VARCHAR(20)
SET @values = '''A'',''B'',''C'''
SET @sqlStr = 'SELECT * FROM #solution WHERE value IN (' + @values + ')'
EXEC sp_ExecuteSql @sqlStr
My preferred method is to use a table function that parses the input values by looping through and locating the delimiter (in this example a comma[,]) and returns a single column with all of the values in.
DECLARE @values VARCHAR(20)
SET @values = 'A,B,C'
SELECT * FROM dbo.fnParseStringForChar(@values,',')
Returns:
value
A
B
C
This can then be used with the WHERE clause:
SELECT *
FROM #solution
WHERE value IN (SELECT * FROM dbo.fnParseStringForChar(@values, ',' ))
See BOL for table functions, control of flow etc. If you REALLY get stuck with it, post back and I'll help out (and I would more than welcome other methods of completing this task
!!! ). Hope this all makes sense! -
Thank you very much, I now use a function I found at
This function can parse very long argument lists, this is why it uses a nested loop.
CREATE FUNCTION charlist_to_table (@list ntext, @delimiter nchar(1) = N',')
RETURNS @tbl TABLE (listpos int IDENTITY(1, 1) NOT NULL, nstr nvarchar(2000))
AS
BEGIN
DECLARE @pos int,
@textpos int,
@chunklen smallint,
@tmpstr nvarchar(4000),
@leftover nvarchar(4000),
@tmpval nvarchar(4000)
SET @textpos = 1
SET @leftover = ''
WHILE @textpos < datalength(@list) / 2
BEGIN
SET @chunklen = 4000 - datalength(@leftover) / 2
SET @tmpstr = @leftover + substring(@list, @textpos, @chunklen)
SET @textpos = @textpos + @chunklen
SET @pos = charindex(@delimiter, @tmpstr)
WHILE @pos > 0
BEGIN
SET @tmpval = ltrim(rtrim(left(@tmpstr, @pos - 1)))
INSERT @tbl (nstr) VALUES(@tmpval)
SET @tmpstr = substring(@tmpstr, @pos + 1, len(@tmpstr))
SET @pos = charindex(@delimiter, @tmpstr)
END
SET @leftover = @tmpstr
END
INSERT @tbl(nstr) VALUES (ltrim(rtrim(@leftover)))
RETURN
END
-
Hi,
lots of info, explanations, tests of performance and various solutions can be found at Erland Sommarskog's pages
You will find several articles there that apply to your situation, especially Lists and Arrays in SQL should be helpful.
Cheers,
Vladan
-
The function I quoted was actually written by Erland Sommarskog.
My code works fine with it.
The website looks interesting.Thanks again!
Gerhard
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply