Dynamic column select - SQL Server Forums

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 2000 Forums

SQL Server Development (2000)

Dynamic column select

Author

Topic

kensai
Posting Yak Master

172 Posts

Posted - 2002-06-06 : 10:58:55

I need to run a query depending on the user choices. I select four columns but one of them is selected based on user choice. For example, I have select a,b,c,d query and I want to select "b" sometimes, but not to select other times. I tried if and case but they both gave me a lot of sytax errors so I assume this isn't the way:


select a,
	if @choice='yes' 
		b,
	c,d
...


select a,
	case @choice
		when 'yes' then b,
	c,d
...

What should I do for this?

M.E.
Aged Yak Warrior

539 Posts

Posted - 2002-06-06 : 11:04:45

declare @sql varchar(8000)
select @sql = 'select a,'
if @choice = yes set @sql= @sql + ' ,b'
select @sql = @sql + ',c,d from blahdeblah'
exec (@sql)

izaltsman
A custom title

1139 Posts

Posted - 2002-06-06 : 11:10:11

Actually your CASE example is pretty close...


select a,
	case @choice
		when 'yes' then b 
                ELSE NULL
        END  as variablecol, 
	c,
        d
---

---
Please note that the column would exist in your final resultset regardless of the value of @choice, but it will contain NULL values whenever @choice<>'yes'. If you want to be able to eliminate the column from the resultset completely, you will need to code two separate SELECTs separated within an IF/ELSE structure:


IF @choice = 'yes'
BEGIN 
   select a,
          b, 
	  c,
          d
   FROM ...
END
ELSE 
BEGIN 
   select a,
	  c,
          d
   FROM ...
END

<edit>or use dynamic SQL like M.E. suggests...(damn sniper!

)</edit>

Edited by - izaltsman on 06/06/2002 11:12:41

M.E.
Aged Yak Warrior

539 Posts

Posted - 2002-06-06 : 11:12:26

Actually, little side note to tack onto here, maybe someone can set me completely straight on this

I know case syntax such as
select 'answer' = case
when answer = 1 then 'yes'
when answer = 0 then 'no'
else 'sure why not'
end
from.....

but something like
select
case
when type = 1 then 'answer'
when type = 2 then 'guess'
end
= '4'
from ....
won't work. Other then using dynamic sql like I did for this answer, is there anyway to use if statements to build @sql to determine which rows are selected?

edit---
Hmm, I sniped izaltsman and then proceded to ask a question which he managed to snipe me on... Okies, that just got confusing. Thanks izaltsman

Edited by - M.E. on 06/06/2002 11:15:54

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources