IN with Parameter

Question

IN with Parameter

Gerhard Schmeusser

SSChasing Mays

Points: 641
More actions
January 12, 2007 at 2:04 am

#118503

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.

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Adrian Nichols-360275 SSCertifiable Points: 7694 More actions · Answer 1

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!

Ade

A Freudian Slip is when you say one thing and mean your mother.
For detail-enriched answers, ask detail-enriched questions...[/url]

Gerhard Schmeusser SSChasing Mays Points: 641 More actions · Answer 2

Thank you very much, I now use a function I found at

http://groups.google.com/group/microsoft.public.sqlserver.reportingsvcs/browse_thread/thread/a5e0fdb24323aa13/e0fcc4c2d38883ec%23e0fcc4c2d38883ec

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

Vladan SSC-Insane Points: 22072 More actions · Answer 3

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

Gerhard Schmeusser SSChasing Mays Points: 641 More actions · Answer 4

The function I quoted was actually written by Erland Sommarskog.

My code works fine with it. The website looks interesting.

Thanks again!

Gerhard