Query About Dynamic Order By ...

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)

Query About Dynamic Order By ...

Author

Topic

peddi_praveen
Starting Member

48 Posts

Posted - 2002-12-18 : 22:34:12

Hi guys,
I have a situation to pass in the parameter to SP to decide the result to be sorted dynamically
I have Implemented in following Way:
Create PROCEDURE SP_XXXXXXXXX
(@in_sort_order INT

)
AS
SELECT col1, col2, col3, col4
FROM table1
ORDER BY CASE
WHEN @in_sort_order = 1 THEN Col2
WHEN @in_sort_order = 2 THEN Col3
WHEN @in_sort_order = 3 THEN Col4
ELSE Col1
END

Please note that Col2, col3, col4 are of INT and Col1 of VARCHAR data type
My dynamic order by is failing and error message is:
SYNTAX ERROR CONVERTING TO VARCHAR to INT

It is working if i remove the ELSE part as all possible cases are of same data type

In brief, Dyamic order by is not working if the columns are of incompatible data types

For this reason, I had to incorporate IF condition for select statement like this..
IF @in_sort_order = 2 or @in_sort_order = 3 or @in_sort_order = 4
BEGIN
SELECT col1, col2, col3, col4
FROM table1
ORDER BY CASE
WHEN @in_sort_order = 1 THEN Col2
WHEN @in_sort_order = 2 THEN Col3
WHEN @in_sort_order = 3 THEN Col4
END
END
ELSE
BEGIN
SELECT col1, col2, col3, col4
FROM table1
ORDER BY Col1
END

I really wonder , why SQL is behaving such and apprecitate your help in writing one SELECT statement which allows Dynamic order by
with columns of different data types....

thnx n regs,
praveen

skillile
Posting Yak Master

208 Posts

Posted - 2002-12-18 : 23:20:31

From BOL

ELSE else_result_expression

Is the expression returned if no comparison operation evaluates to TRUE. If this argument is omitted and no comparison operation evaluates to TRUE, CASE returns NULL. else_result_expression is any valid SQL Server expression. The data types of else_result_expression and any result_expression must be the same or must be an implicit conversion.

You could however user dynamic sql.

declare @sql...,
@order varchar(25),
@val int

SET @order = CASE @val
WHEN 1 THEN 'order by field1'
WHEN 2 THEN 'order by field2'
...
END

set @sql = 'select * from table ' + @order

EXEC (@sql)

slow down to move faster...

robvolk
Most Valuable Yak

15732 Posts

Posted - 2002-12-18 : 23:23:23

A single ORDER BY expression must be of a single data type. You have varchar and int mixed in with the CASE statement, and the optimizer can't process it correctly.

There are two options:

SELECT col1, col2, col3, col4
FROM table1
ORDER BY CASE
WHEN @in_sort_order = 1 THEN Str(Col2,10,0)
WHEN @in_sort_order = 2 THEN Str(Col3,10,0)
WHEN @in_sort_order = 3 THEN Str(Col4,10,0)
ELSE Col1
END

This uses the Str() function to convert the integers to character strings, with leading spaces so that they sort correctly. However, this method cannot utilize any idexes on those columns. This next one may be able to:

SELECT col1, col2, col3, col4
FROM table1
ORDER BY
CASE WHEN @in_sort_order NOT IN (1,2,3) THEN Col1 ELSE Null END,
CASE WHEN @in_sort_order = 1 THEN Col2
WHEN @in_sort_order = 2 THEN Col3
WHEN @in_sort_order = 3 THEN Col4
ELSE Null END

This separates the varchar and int columns so that neither need to be converted to the other.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources