SELECT IN List Query - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

SELECT IN List Query

Author

Topic

MDCas
Starting Member

5 Posts

Posted - 2008-04-02 : 05:52:17

I have a table that has a list of skills, ie "HP One", "HP Two"

I need to pass the these skills from my applications search page to a stored procedure, hence I have a snippet of the SP below.

DECLARE @SkillSet NVarChar (200)
SET @SkillSet = '-HP One-,-HP Two-'
SET @SkillSet = Replace(@SkillSet, '-', '''')
SELECT * FROM CPSkillMatrixLevels WHERE SkillMatrixLevelName IN (@SkillSet)

The following sp does not return any results, but when i set the last line to ..

SELECT * FROM CPSkillMatrixLevels WHERE SkillMatrixLevelName IN ('HP One', 'HP Two')

It returns a set of results. Also when I do a select on the variable @SkillSet, ie SELECT @SkillSet, it displays 'HP One', 'HP Two'

Can enybody help me here, i Know i'm doing something wrong, but I cant think of what it is.

Thanks

nr
SQLTeam MVY

12543 Posts

Posted - 2008-04-02 : 05:59:52

Replace @s with @SkillSet

;with csvtbl(i,j, s)
as
(
select i=1, s=charindex(',',@s+','), substring(@s, 1, charindex(',',@s+',')-1)
union all
select i=j+1, j=charindex(',',@s+',',j+1), substring(@s, j+1, charindex(',',@s+',',j+1)-(j+1))
from csvtbl where charindex(',',@s+',',j+1) <> 0
)
SELECT * FROM CPSkillMatrixLevels WHERE SkillMatrixLevelName IN (select s from csvtbl)

or
exec ('SELECT * FROM CPSkillMatrixLevels WHERE SkillMatrixLevelName IN (' + @SkillSet + ')')

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2008-04-02 : 06:24:02

Also refer http://www.sommarskog.se/arrays-in-sql.html

Madhivanan

Failing to plan is Planning to fail

ranganath
Posting Yak Master

209 Posts

Posted - 2008-04-02 : 07:05:59

Hi,
Try with this also

SELECT * FROM CPSkillMatrixLevels
WHERE ',' + @SkillSet + ',' LIKE '%,' + CAST(SkillMatrixLevelName AS VARCHAR(200)) + ',%'

MDCas
Starting Member

5 Posts

Posted - 2008-04-02 : 07:16:10

Thank you for all your responses. In the end, i've looked at the article in http://www.sommarskog.se/arrays-in-sql.html recommended by Madhivanan.

I've created a function called CreateTableForList that parses the list and returns a table, then i've used

SELECT * FROM CPSkillMatrixLevels WHERE SkillMatrixLevelName IN
(
SELECT * FROM CreateTableForList(@SkillSet)
)

the function (lifted from the reference above) is:
------------------------------------------------------

CREATE FUNCTION CreateTableForList (@list nvarchar(MAX))
RETURNS @tbl TABLE (NumberValues int NOT NULL) AS
BEGIN
DECLARE @pos int,
@nextpos int,
@valuelen int

SELECT @pos = 0, @nextpos = 1

WHILE @nextpos > 0
BEGIN
SELECT @nextpos = charindex(',', @list, @pos + 1)
SELECT @valuelen = CASE WHEN @nextpos > 0
THEN @nextpos
ELSE len(@list) + 1
END - @pos - 1
INSERT @tbl (NumberValues)
VALUES (convert(int, substring(@list, @pos + 1, @valuelen)))
SELECT @pos = @nextpos
END
RETURN
END
------------------------------------------------------------

nr
SQLTeam MVY

12543 Posts

Posted - 2008-04-02 : 07:20:09

Have a look at the CTE I posted above.
If you want to use a function instead of in-line code then this should be faster (and simpler) than the code you have.
There's a more flexible one here
http://www.nigelrivett.net/SQLTsql/ParseCSVString.html
There are methods of doing this faster than with CTE if that's an issue but they tend to be a bit complicated.
In v2005 you usually wouldn't bother creating a function to encapsulate this code.

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

MDCas
Starting Member

5 Posts

Posted - 2008-04-02 : 07:40:55

Thanks again nr, I tried to use the inline solution you posted but i could not get it to work. I wanted to use a function so that I can re-use it in other sp's. If you say the function in http://www.nigelrivett.net/SQLTsql/ParseCSVString.html is faster, then it is. Hence i've changed my function to use that logic instead, plus it's a more flexible aproach.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources