April 3, 2009 at 9:03 am
I'm trying to figure out how to dynamically create a list of columns for comparison in an IN statement. I don't particularly want to write a dynamic SQL statement, though. I've handled passing in a list of values for comparison; split the list into a table and do a select against it. But how do you manage column names?
Each item in the list gets typed by the parser, right? So, in (23,17,9), the parser sees an array of ints. When it's ('a','b','c') it's an array of strings. That's why you can't do @var='23,17,9' for (@var) - the list doesn't get parsed into an array. So, what do I do with column names?
April 3, 2009 at 9:06 am
If you're talking about having a variable list of column names as an input parameter, you'll have to use dynamic SQL for that.
One thing I'll recommend is parse the string, and join it to sys.columns, to make sure you're getting actual column names.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
April 3, 2009 at 9:21 am
Gus is right. You want to do something dynamically without using dynamic SQL.
If I read what you want correctly, you want to produce something like this
WHERE @var in (@listOfColumnNames).
You can't get there from here.
You do have an alternative, if there is a limited number of columns to test. That would be to code various combinations of select statements and pass a parameter to choose the appropriate combination. Something like this:
if @option = 1
begin
select foo
from bar
where @value in (fooCol1, fooCol2)
end
else
begin
if @option = 2
begin
select foo
from bar
where @value in (fooCol3, fooCol4)
end
end
If you think to yourself "I don't want to type that much." remember that you can always write code to write the code for you 😉 Cut and paste it into a CREATE PROCEDURE job and you're done. No dynamic SQL in production.
__________________________________________________
Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills
April 3, 2009 at 9:31 am
Well, ***!*** What I'm trying to do is dynamically generate a list of columns for a pivot. The columnnames, of course, are actually values. So they change all the time. Oh well.
April 3, 2009 at 9:40 am
Same problem. Sorry about that. I was originally excited about PIVOT until I realized that it wasn't truly dynamic like pivot tables in Excel. Now I stay with the old-school CASE statement approach because it runs faster.
__________________________________________________
Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills
April 3, 2009 at 9:46 am
Simple-Talk.com has a pivot workshop by Phil and Robyn that has code for a dynamic pivot in SQL.
Personally, I view pivots as an application task. Reporting Services, Excel, etc., all do pivots better than SQL does.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
April 3, 2009 at 9:51 am
I checked it out. Thanks Gus.
The article describes the way I've always done it, but it's still using dynamic SQL. I will have to remember that people use the term Crosstab for using a bunch of CASE statements.
__________________________________________________
Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply