Comma-delimited list parameter in WHERE clause

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 2008 Forums

Transact-SQL (2008)

Comma-delimited list parameter in WHERE clause

Author

Topic

Hammerklavier
Starting Member

26 Posts

Posted - 2012-06-19 : 13:49:25

Hi,

I'm creating a stored procedure. Users will need to be able to enter multiple values for the parameter in a manner like this: "013B, C298, 883K".

Then I would put this in an IN statement in my WHERE clause, so it would read something like this:

SELECT *
FROM MyTable
WHERE MyField IN (@MyParameter)

The problem, of course, is that the users won't be putting an apostrophe around each of their values in the comma-delimited list. Somehow, my stored procedure will need to be able to split this parameter string based on the comma, then put apostrophes around each value, then string them back together so they can go into the IN clause.

Any ideas on how this might be accomplished? I've played around a bit with table variables and dynamic SQL, but I'm having trouble envisioning how to get started with something like this. Any help would be greatly appreciated!

Thank you.

DonAtWork
Master Smack Fu Yak Hacker

2167 Posts

Posted - 2012-06-19 : 14:28:07

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=50648

How to ask: http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-06-19 : 15:10:08

[code]
SELECT *
FROM MyTable
WHERE ',' + @MyParameter + ',' LIKE '%,' + CAST(MyField AS varchar(100)) + ',%'
[/code]

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Hammerklavier
Starting Member

26 Posts

Posted - 2012-06-20 : 13:54:26

Thank you for the responses. visakh16, your solution is particularly intriguing due to its simplicity. Are there any drawbacks to it? I've modified the where clause as follows:

WHERE ',' + @MyParameter + ',' LIKE '%,' + CAST(MyField AS VARCHAR(100)) + ',%'
OR ',' + @MyParameter LIKE '%,' + CAST(MyField AS VARCHAR(100))

This would prevent users from having to counterintuitively enter a comma at the end. Testing it, it seems to work so far.

LoztInSpace
Aged Yak Warrior

940 Posts

Posted - 2012-06-20 : 19:22:16

The drawback is that it won't be able to use an index. That may or may not matter depending on many factors such as your table size and performance requirements.
Personally I'd fix up the parameter in code before passing it in to avoid the "OR" part. It won't make any difference here because of the table scan though.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources