SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Comma separated lists


Comma separated lists

Author
Message
richardmgreen1
richardmgreen1
Ten Centuries
Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)

Group: General Forum Members
Points: 1081 Visits: 962
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?
Sean Lange
Sean Lange
One Orange Chip
One Orange Chip (27K reputation)One Orange Chip (27K reputation)One Orange Chip (27K reputation)One Orange Chip (27K reputation)One Orange Chip (27K reputation)One Orange Chip (27K reputation)One Orange Chip (27K reputation)One Orange Chip (27K reputation)

Group: General Forum Members
Points: 27320 Visits: 17557
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.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
richardmgreen1
richardmgreen1
Ten Centuries
Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)

Group: General Forum Members
Points: 1081 Visits: 962
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
Sean Lange
One Orange Chip
One Orange Chip (27K reputation)One Orange Chip (27K reputation)One Orange Chip (27K reputation)One Orange Chip (27K reputation)One Orange Chip (27K reputation)One Orange Chip (27K reputation)One Orange Chip (27K reputation)One Orange Chip (27K reputation)

Group: General Forum Members
Points: 27320 Visits: 17557
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.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
Sean Lange
Sean Lange
One Orange Chip
One Orange Chip (27K reputation)One Orange Chip (27K reputation)One Orange Chip (27K reputation)One Orange Chip (27K reputation)One Orange Chip (27K reputation)One Orange Chip (27K reputation)One Orange Chip (27K reputation)One Orange Chip (27K reputation)

Group: General Forum Members
Points: 27320 Visits: 17557
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.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
richardmgreen1
richardmgreen1
Ten Centuries
Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)

Group: General Forum Members
Points: 1081 Visits: 962
Thanks Sean

Looks like the splitter was what I needed so I'm sorted.
Thanks for your help.
Sean Lange
Sean Lange
One Orange Chip
One Orange Chip (27K reputation)One Orange Chip (27K reputation)One Orange Chip (27K reputation)One Orange Chip (27K reputation)One Orange Chip (27K reputation)One Orange Chip (27K reputation)One Orange Chip (27K reputation)One Orange Chip (27K reputation)

Group: General Forum Members
Points: 27320 Visits: 17557
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.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search