Passing a variable for ORDER BY/ using CASE

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

Passing a variable for ORDER BY/ using CASE

Author

Topic

cimscims
Starting Member

2 Posts

Posted - 2010-06-11 : 09:29:30

There are three sets of dropdowns in my front end
1) SortBy1, SortOrder1
2) SortBy2, SortOrder2
3) SortBy3, SortOrder3

Which has the follwing options
1) ID
2) IssuedDate
3) Size
4) Make

User can select either one or more than a set of drodowns say

1) ID ASC
2) ID ASC, IssuedDate DESC
3) ID ASC, IssuedDate DESC, Size DESC
etc.. like above, there are many combinations

Limitations:
- the query should not use 'EXEC' statement. I am using this query for reporting purpose and if i use EXEC then the columns were not showing up for drag-drop into my report.

Also i have tried using CASE statement:
CASE WHEN @sortorder1 = 'ASC' AND @sortby1 = 'ID' THEN ID END ASC,
CASE WHEN @sortorder1 = 'DESC' AND @sortby1 = 'ID' THEN ID END DESC,
CASE WHEN @sortorder2 = 'ASC' AND @sortby2 = 'ID' THEN ID END ASC,
CASE WHEN @sortorder2 = 'DESC' AND @sortby2 = 'ID' THEN ID END DESC

I got an error 'A column has been specified more than once in the order by list. Columns in the order by list must be unique'

Could some one help me out. Thanks in advance.

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2010-06-14 : 03:52:09

The only possible case I think of is to use dynamic SQL

Madhivanan

Failing to plan is Planning to fail

Sachin.Nand

2937 Posts

Posted - 2010-06-14 : 05:32:56

Maybe something like this


select *,ROW_NUMBER()over(order by 
         case when @sortorder1='DESC' then  
                  case when @sortby1='ID' then ID end 
                  case when @sortby2='ID' then ID end 
         end desc from table

Same way you can do it for @sortorder2 also

Limitations live only in our minds. But if we use our imaginations, our possibilities become limitless.

PBUH

cimscims
Starting Member

2 Posts

Posted - 2010-06-16 : 14:27:03

madhivanan, I don't want to use dynamic SQL as i am using the query for reports. If i use dynamic SQL, the columns are unavailable. I need a sql query. The query is

SELECT
Id,
Size
FROM
[dbo].[tblFlashDrive]
WHERE
Size = '2GB'
ORDER BY
CASE WHEN @sortorder1 = 'ASC' AND @sortby1 = 'ID' THEN ID END ASC,
CASE WHEN @sortorder1 = 'DESC' AND @sortby1 = 'ID' THEN ID END DESC,
CASE WHEN @sortorder2 = 'ASC' AND @sortby2 = 'ID' THEN ID END ASC,
CASE WHEN @sortorder2 = 'DESC' AND @sortby2 = 'ID' THEN ID END DESC

Idera, I could not understand how to use the query you gave. Could you please modify my query.

Sachin.Nand

2937 Posts

Posted - 2010-06-16 : 14:59:00

Can you please tell me why have you used two variables for sortorder i.e sortorder1 and sortorder2 when the same can be achieved with using just one variable say sortorder.Also Doesnt make any sense to me for using two @sortby as you are sorting on the same column ID for all the conditions.



declare @sortorder as varchar(30)='DESC'
declare @sortby1 as varchar(30)='ID'
declare @sortby2 as varchar(30)='ID'

declare @tbl as table(id int,size varchar(30))
insert into @tbl
select 1,'1GB' union all
select 2,'2GB' union all
select 3,'4GB' union all
select 4,'8GB' 

select id,size,
ROW_NUMBER()over(order by 
         case @sortorder when 'ASC' then  
                  case when @sortby1='ID' or @sortby2='ID' then id  end 
                 
                  end,
              case @sortorder   when 'DESC' then  
                   case when  @sortby1='ID' or @sortby2='ID' then id end 
                 end desc 
           )
 from @tbl

Limitations live only in our minds. But if we use our imaginations, our possibilities become limitless.

PBUH

X002548
Not Just a Number

15586 Posts

Posted - 2010-06-16 : 15:05:10

so code three different sql statements and pass in a variable

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Add yourself!
http://www.frappr.com/sqlteam

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2010-06-16 : 15:23:39

If you had 3 parameters something like @Sort1, @Sort2 and @Sort3 that have values like 'ID ASC' and/or 'IssuedDate DESC' the you could use this kinda of ORDER BY clause:

ORDER BY
	CASE 
		WHEN @Sort1 = 'ID ASC' THEN CAST(IssuedDate AS NVARCHAR(MAX))
		WHEN @Sort1 = 'IssuedDate ASC' THEN CAST(IssuedDate AS NVARCHAR(MAX))
		WHEN @Sort1 = 'Size ASC' THEN CAST(Size AS NVARCHAR(MAX))
		WHEN @Sort1 = 'Make ASC' THEN CAST(Make AS NVARCHAR(MAX))
	END ASC,
	CASE 
		WHEN @Sort1 = 'ID DESC' THEN CAST(IssuedDate AS NVARCHAR(MAX))
		WHEN @Sort1 = 'IssuedDate DESC' THEN CAST(IssuedDate AS NVARCHAR(MAX))
		WHEN @Sort1 = 'Size DESC' THEN CAST(Size AS NVARCHAR(MAX))
		WHEN @Sort1 = 'Make DESC' THEN CAST(Make AS NVARCHAR(MAX))
	END DESC,
	CASE 
		WHEN @Sort2 = 'ID ASC' THEN CAST(IssuedDate AS NVARCHAR(MAX))
		WHEN @Sort2 = 'IssuedDate ASC' THEN CAST(IssuedDate AS NVARCHAR(MAX))
		WHEN @Sort2 = 'Size ASC' THEN CAST(Size AS NVARCHAR(MAX))
		WHEN @Sort2 = 'Make ASC' THEN CAST(Make AS NVARCHAR(MAX))
	END ASC,
	CASE 
		WHEN @Sort2 = 'ID DESC' THEN CAST(IssuedDate AS NVARCHAR(MAX))
		WHEN @Sort2 = 'IssuedDate DESC' THEN CAST(IssuedDate AS NVARCHAR(MAX))
		WHEN @Sort2 = 'Size DESC' THEN CAST(Size AS NVARCHAR(MAX))
		WHEN @Sort2 = 'Make DESC' THEN CAST(Make AS NVARCHAR(MAX))
	END DESC,
	CASE 
		WHEN @Sort3 = 'ID ASC' THEN CAST(IssuedDate AS NVARCHAR(MAX))
		WHEN @Sort3 = 'IssuedDate ASC' THEN CAST(IssuedDate AS NVARCHAR(MAX))
		WHEN @Sort3 = 'Size ASC' THEN CAST(Size AS NVARCHAR(MAX))
		WHEN @Sort3 = 'Make ASC' THEN CAST(Make AS NVARCHAR(MAX))
	END ASC,
	CASE 
		WHEN @Sort3 = 'ID DESC' THEN CAST(IssuedDate AS NVARCHAR(MAX))
		WHEN @Sort3 = 'IssuedDate DESC' THEN CAST(IssuedDate AS NVARCHAR(MAX))
		WHEN @Sort3 = 'Size DESC' THEN CAST(Size AS NVARCHAR(MAX))
		WHEN @Sort3 = 'Make DESC' THEN CAST(Make AS NVARCHAR(MAX))
	END DESC

Obviously, you should adjust NVARCAHR(MAX) to what makes sense.

robvolk
Most Valuable Yak

15732 Posts

Posted - 2010-06-16 : 15:34:42

CASTing them will mess up the ordering of non-character data. CAST also doesn't format dates as sortable values.

Binary won't work either, as it will be case sensitive, and binary representations do not always sort properly as bytes (ints, GUIDs, etc.)

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2010-06-16 : 16:25:51

Agreed. As stated by Madhivanan, Dynamic SQL is the only way I know of to do this sort of thing.

One suggestion on my example is to possibly try CASTing as a SQL_VARIANT. I've not messed with SQL_VARIANT much, but seem to remember that sorting will work correctly. (but I could be wrong).

robvolk
Most Valuable Yak

15732 Posts

Posted - 2010-06-16 : 16:42:47

Doesn't seem like it does, unfortunately:

create table #a(i int not null identity(1,1) primary key, col sql_variant null,
Type as sql_variant_property(col, 'BaseType'), Precision as sql_variant_property(col, 'Precision'), 
Scale as sql_variant_property(col, 'Scale'), MaxLength as sql_variant_property(col, 'MaxLength'), 
Bytes as sql_variant_property(col, 'TotalBytes'), bin as cast(col as varbinary(8000)) )
insert #a(col) values('ABC')
insert #a(col) values('123')
insert #a(col) values('-123')
insert #a(col) values(123)
insert #a(col) values(-123)
insert #a(col) values(123.0000)
insert #a(col) values(-123.0000)
insert #a(col) values(getdate())

select * from #a order by col
select * from #a order by bin

It doesn't follow a binary sorting pattern either.

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2010-06-16 : 17:41:50

Yeah, I'm not sure about storing the values as SQL_VARIANT, but if you cast an alreasy typed value it seems to work on a INTs, VARCHARs and DATEIME. I am pretty sick right now, so the drugs might be fogging my mind... :)

DECLARE @Foo TABLE (Val VARCHAR(100), Val1 INT, Val2 DATETIME)
INSERT @Foo
SELECT '1', 1, GETDATE() - 10
UNION ALL SELECT '2', 2, GETDATE() + 40
UNION ALL SELECT '3', 3, GETDATE() - 20
UNION ALL SELECT '10', 10, GETDATE() + 11
UNION ALL SELECT '14', 14, GETDATE() - 19
UNION ALL SELECT '100', 100, GETDATE() + 20
UNION ALL SELECT '101', 101, GETDATE() - 2
UNION ALL SELECT 'a', 900, GETDATE() + 3
UNION ALL SELECT 'b', -1, GETDATE() - 17
UNION ALL SELECT 'abc', 101, GETDATE() + 9
UNION ALL SELECT '@#@', -90, GETDATE() - 6



-- Int Sort
SELECT Val, Val1, Val2
FROM @Foo
ORDER BY CAST(Val AS SQL_VARIANT) ASC

SELECT Val, Val1, Val2
FROM @Foo
ORDER BY Val ASC

-- Varchar Sort
SELECT Val, Val1, Val2
FROM @Foo
ORDER BY CAST(Val1 AS SQL_VARIANT) ASC

SELECT Val, Val1, Val2
FROM @Foo
ORDER BY Val1 ASC

-- Datetime sort
SELECT Val, Val1, Val2
FROM @Foo
ORDER BY CAST(Val2 AS SQL_VARIANT) ASC

SELECT Val, Val1, Val2
FROM @Foo
ORDER BY Val2 ASC

robvolk
Most Valuable Yak

15732 Posts

Posted - 2010-06-16 : 17:55:26

That's handy! I guess the sql_variant overhead is included in the table, but not when it's converted, and that may be why storing it messes up the ordering.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources