case structure query - 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

General SQL Server Forums

New to SQL Server Programming

case structure query

Author

Topic

soorajtnpki
Posting Yak Master

231 Posts

Posted - 2008-12-10 : 06:33:55

hi all,

see below query give error

declare @SortOrder varchar(20)
set @SortOrder='asc'

select salesmanname,rate as Priority
from sales
order by
case when @SortOrder = 'first' then Priority end
case when @SortOrder = 'asc' then salesmanname,Priority ASC end
case when @SortOrder = 'desc' then customers.salesmanname DESC,Priority ASC end

tanx in advance

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-12-10 : 06:39:18

Did you check Books Online first? The Microsoft SQL Server helpfile...

select		salesmanname,
		rate as Priority 
from		sales
order by	case @SortOrder 
			when 'first' then rate
			when 'asc' then salesmanname
			else null
		end,
		case @SortOrder 
			when 'desc' then salesmanname
			else null
		end  DESC,
		Priority

E 12°55'05.63"
N 56°04'39.26"

soorajtnpki
Posting Yak Master

231 Posts

Posted - 2008-12-10 : 06:59:40

hi peso
thanks

soorajtnpki
Posting Yak Master

231 Posts

Posted - 2008-12-10 : 08:00:22

quote:
Originally posted by Peso

Did you check Books Online first? The Microsoft SQL Server helpfile...
select salesmanname,
rate as Priority
from sales
order by case @SortOrder
when 'first' then rate
when 'asc' then salesmanname
else null
end,
case @SortOrder
when 'desc' then salesmanname
else null
end DESC,
Priority

E 12°55'05.63"
N 56°04'39.26"

hi we can further reduce to the following

select salesmanname,
rate as Priority
from sales
order by case @SortOrder
when 'asc' then salesmanname
else null
end, case @SortOrder
when 'desc' then salesmanname
else null
end DESC,
Priority
am i right?

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2008-12-10 : 08:19:26

quote:
Originally posted by soorajtnpki

quote:
Originally posted by Peso

Did you check Books Online first? The Microsoft SQL Server helpfile...
select salesmanname,
rate as Priority
from sales
order by case @SortOrder
when 'first' then rate
when 'asc' then salesmanname
else null
end,
case @SortOrder
when 'desc' then salesmanname
else null
end DESC,
Priority

E 12°55'05.63"
N 56°04'39.26"

hi we can further reduce to the following

select salesmanname,
rate as Priority
from sales
order by case @SortOrder
when 'asc' then salesmanname
else null
end, case @SortOrder
when 'desc' then salesmanname
else null
end DESC,
Priority
am i right?