Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Comma separated lists Expand / Collapse
Author
Message
Posted Friday, April 19, 2013 6:37 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Yesterday @ 7:47 AM
Points: 294, Visits: 368
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?
Post #1444353
Posted Friday, April 19, 2013 7:25 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 8:03 AM
Points: 13,128, Visits: 11,966
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 Moden's 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)
Post #1444383
Posted Friday, April 19, 2013 7:33 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Yesterday @ 7:47 AM
Points: 294, Visits: 368
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
Post #1444390
Posted Friday, April 19, 2013 7:42 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 8:03 AM
Points: 13,128, Visits: 11,966
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 Moden's 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)
Post #1444400
Posted Friday, April 19, 2013 7:44 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 8:03 AM
Points: 13,128, Visits: 11,966
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 Moden's 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)
Post #1444402
Posted Friday, April 19, 2013 8:35 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Yesterday @ 7:47 AM
Points: 294, Visits: 368
Thanks Sean

Looks like the splitter was what I needed so I'm sorted.
Thanks for your help.
Post #1444457
Posted Friday, April 19, 2013 8:38 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 8:03 AM
Points: 13,128, Visits: 11,966
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 Moden's 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)
Post #1444460
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse