Comma separated lists

Question

Comma separated lists

richardmgreen1

SSChampion

Points: 10356
More actions
April 19, 2013 at 6:37 am

#292772

Hi all
I've got a couple of queries that use quite large comma separated lists in the WHERE clauses.
I've seen (somewhere) a way of putting these into some sort of variable and using the variable instead of the entire list in the WHERE clause.
Would someone be kind enough to point me in the right direction?

Viewing 7 posts - 1 through 6 (of 6 total)

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

Sean Lange SSC Guru Points: 286590 More actions · Answer 1

richardmgreen1 (4/19/2013)
Hi all
I've got a couple of queries that use quite large comma separated lists in the WHERE clauses.
I've seen (somewhere) a way of putting these into some sort of variable and using the variable instead of the entire list in the WHERE clause.
Would someone be kind enough to point me in the right direction?

Not quite sure what you mean. If you used a variable you would have to use dynamic sql and I don't think that is what you want here.

If you mean you are passing a delimited list as a string you can parse string. If that is the case you can check the article in my signature about splitting strings.

If you could post some code or some more details about what you are trying to do I will be happy to help.

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

richardmgreen1 SSChampion Points: 10356 More actions · Answer 2

Hi Sean

What I'm trying to do is use some sort of variable in the where clause instead of a huge list of employee IDs (I'm open to suggestions).

One of the queries I need to update has the same set of employee IDs in twice (the where clause is split by an OR) which, as you can probably imagine) makes life a bit awkward.

The rough syntax of what I want to do is this:-

declare @variable varchar(1000)

set @variable='3661214,1604552,1604914,1604909,1602526,1602181,9667061,1600558'

select

columns here

from

table here

where

employee ID in @variable

Sean Lange SSC Guru Points: 286590 More actions · Answer 3

richardmgreen1 (4/19/2013)
Hi Sean
What I'm trying to do is use some sort of variable in the where clause instead of a huge list of employee IDs (I'm open to suggestions).
One of the queries I need to update has the same set of employee IDs in twice (the where clause is split by an OR) which, as you can probably imagine) makes life a bit awkward.
The rough syntax of what I want to do is this:-
declare @variable varchar(1000)
set @variable='3661214,1604552,1604914,1604909,1602526,1602181,9667061,1600558'
select
columns here
from
table here
where
employee ID in @variable

As I said in my first post you can't use a variable like that. You have to either parse the string or use dynamic sql. It is hard to say what the best approach is because your example is so out of context.

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

Sean Lange SSC Guru Points: 286590 More actions · Answer 4

Something like this might help.

declare @variable varchar(1000)

set @variable='3661214,1604552,1604914,1604909,1602526,1602181,9667061,1600558'

select

columns here

from

table here

cross apply dbo.DelimitedSplit8K(@variable, ',')

You will need to read the article in my signature about splitting strings. In that article you will find the code to create the DelimitedSplit8K function.

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

richardmgreen1 SSChampion Points: 10356 More actions · Answer 5

Thanks Sean

Looks like the splitter was what I needed so I'm sorted.

Thanks for your help.

Sean Lange SSC Guru Points: 286590 More actions · Answer 6

You are welcome, glad that worked for you. Make sure you understand how that thing works.

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/