IN operator and parmaterized list

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)

IN operator and parmaterized list

Author

Topic

kpgraci
Yak Posting Veteran

68 Posts

Posted - 2010-12-14 : 16:17:48

I have a query:

SELECT FormName FROM Forms WHERE FormKey IN (@FormList)

The idea is that @FormList is a comma separated list of form keys, like: 'abc,def,ghi'

This qurey works when @FormList is one key, like 'abc' but returns nothing when it is more than one key. I can see why, no form key has 'abc,def,ghi' in it. (I also tried "'abc','def','ghi'" - adding quotes)

I don't know in advance how many keys in the list there will be. I'm using stored procedures, otherwise I could manually build the query in code to look like this: (which I'm sure will work)

SELECT FormName FROM Forms WHERE FormKey IN ('abc',def',ghi')

How can this be done in a stored procedure with only the list @FormList being passed?

kpg

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2010-12-14 : 19:24:30

There are a couple of options for dealing with delimited lists and each method has its pros and cons:
1. Use Dynamic SQL.
2. Use a Parsing/Split function to parse your string into its base elements.
3. Use a "backwards LIKE" technique, for example: WHERE ',' + @FormList + ',' LIKE '%,' + FormKey + ',%

However, if you can, you should not pass a delimited list. Rather, you should use a table-valued parameter.

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2010-12-15 : 01:34:36

Here is a good link about table-valued parameters:
http://www.sqlteam.com/article/sql-server-2008-table-valued-parameters

No, you're never too old to Yak'n'Roll if you're too young to die.

jcelko
Esteemed SQL Purist

547 Posts

Posted - 2010-12-15 : 13:10:55

This approach to SQL is fundamentally wrong. Your mindset is still stuck in 1960's versions of BASIC and other interpreted languages. Read theses article:

http://www.simple-talk.com/sql/learn-sql-server/values()-and-long-parameter-lists/

http://www.simple-talk.com/sql/learn-sql-server/values()-and-long-parameter-lists---part-ii/

--CELKO--
Books in Celko Series for Morgan-Kaufmann Publishing
Analytics and OLAP in SQL
Data and Databases: Concepts in Practice
Data, Measurements and Standards in SQL
SQL for Smarties
SQL Programming Style
SQL Puzzles and Answers
Thinking in Sets
Trees and Hierarchies in SQL

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources