Order by using @Params

AndyRayner

Hello,

Basically I am trying to do an order by statement using variables.

I have the SQL:

IF @SortOrder = 'ASC'

BEGIN

SELECT DISTINCT D.Code as 'T1', D.CompanyName as 'T2',

S.SpendAmount as 'T3', E.SpendAmount as 'T4',

E.SpendAmount - S.SpendAmount as 'T5',

Case When S.SpendAmount > 0 Then ((E.SpendAmount - S.SpendAmount)/S.SpendAmount) * 100

When S.SpendAmount = 0 Then ((E.SpendAmount - S.SpendAmount)/1)

When E.SpendAmount > 0 Then ((E.SpendAmount - S.SpendAmount)/E.SpendAmount) * 100

When E.SpendAmount = 0 Then ((E.SpendAmount - S.SpendAmount)/1)

END as 'T6'

FROM DivisionData D,

(SELECT Code, SpendAmount FROM DivisionData WHERE Year = @StartYear and Month = @StartMonth and Division = @Division) S,

(SELECT Code, SpendAmount FROM DivisionData WHERE Year = @EndYear and Month = @EndMonth and Division = @Division) E

WHERE D.Division = @Division

AND S.Code = D.Code

AND E.Code = D.Code

END

I have the params @Crit1 @Crit2 .... to @Crit6. These parameters come from a web form and can have the values 'T1' 'T2'... to 'T6' They correspond to the field names I have put in my select statement.

What I want to do is use these params in an Order By statement, enabling the user to select how the fields are ordered.

I have tried using something along the lines of:

ORDER BY

CASE WHEN @Crit1 = 'T1'

THEN 'T1' END

This works as long as I dont use the word DISTINCT in my select statement, otherwise I get the error:

Msg 145, Level 15, State 1, Line 4

ORDER BY items must appear in the select list if SELECT DISTINCT is specified.

I do need the distinct... I was wondering if there is a simple way to get round this, or if anyone can point me in the right direction or a article/example of this

Many thanks

Andrew Rayner

Chris Howarth

You might have further issues here as all elements of a CASE statement should return the same datatype - if not then implicit conversion will be performed in accordance with datatype precedence.

e.g. in your SELECT list, presumably CompanyName is a text string and SpendAmount is a numerical value. I would expect the CASE statement in your ORDER BY to attempt to convert CompanyName to an integer, which will more than likely fail.

There are at least a couple of approaches that you could take:

1. Copy the query and amend appropriately x number of times, where x is the number of potential ORDER BY combinations that you could have. Execute the required query in accordance with the supplied parameters. This could get messy if you have a lot of potential combinations.

2. Build up a string containing your query, append the appropriate ORDER BY and execute via sp_executesql. Be careful to trap potential SQL injection attempts.

Have a look here for more info on dynamic SQL:

http://www.sommarskog.se/dynamic_sql.html

Chris

zuomin

Dynamic SQL may solve your problem.

Also sp_executesql will be useful to you.

Thanks,
Zuomin

DaleJ

It's a little ugly, but, how about this:

Code Block

IF @SortOrder = 'ASC'

BEGIN

SELECT T1, T2, T3, T4, T5, T6

FROM

(

SELECT DISTINCT D.Code as 'T1', D.CompanyName as 'T2',

S.SpendAmount as 'T3', E.SpendAmount as 'T4',

E.SpendAmount - S.SpendAmount as 'T5',

Case When S.SpendAmount > 0 Then ((E.SpendAmount - S.SpendAmount)/S.SpendAmount) * 100

When S.SpendAmount = 0 Then ((E.SpendAmount - S.SpendAmount)/1)

When E.SpendAmount > 0 Then ((E.SpendAmount - S.SpendAmount)/E.SpendAmount) * 100

When E.SpendAmount = 0 Then ((E.SpendAmount - S.SpendAmount)/1)

END as 'T6'

FROM DivisionData D,

(SELECT Code, SpendAmount FROM DivisionData WHERE Year = @StartYear and Month = @StartMonth and Division = @Division) S,

(SELECT Code, SpendAmount FROM DivisionData WHERE Year = @EndYear and Month = @EndMonth and Division = @Division) E

WHERE D.Division = @Division

AND S.Code = D.Code

AND E.Code = D.Code

) as Data

ORDER BY

CASE @Crit1 WHEN 'T1' THEN T1

WHEN 'T2' THEN T2

WHEN 'T3' THEN T3

WHEN 'T4' THEN T4

WHEN 'T5' THEN T5

WHEN 'T6' THEN T6

END,

CASE @Crit2 WHEN 'T1' THEN T1

WHEN 'T2' THEN T2

WHEN 'T3' THEN T3

WHEN 'T4' THEN T4

WHEN 'T5' THEN T5

WHEN 'T6' THEN T6

END,

CASE @Crit3 WHEN 'T1' THEN T1

WHEN 'T2' THEN T2

WHEN 'T3' THEN T3

WHEN 'T4' THEN T4

WHEN 'T5' THEN T5

WHEN 'T6' THEN T6

END,

CASE @Crit4 WHEN 'T1' THEN T1

WHEN 'T2' THEN T2

WHEN 'T3' THEN T3

WHEN 'T4' THEN T4

WHEN 'T5' THEN T5

WHEN 'T6' THEN T6

END,

CASE @Crit5 WHEN 'T1' THEN T1

WHEN 'T2' THEN T2

WHEN 'T3' THEN T3

WHEN 'T4' THEN T4

WHEN 'T5' THEN T5

WHEN 'T6' THEN T6

END,

CASE @Crit6 WHEN 'T1' THEN T1

WHEN 'T2' THEN T2

WHEN 'T3' THEN T3

WHEN 'T4' THEN T4

WHEN 'T5' THEN T5

WHEN 'T6' THEN T6

END

Chris Howarth

DaleJ - this falls fowl of the issue I described above surrounding CASE statements and datatypes.

Try the example below to see what I mean.

Cheers

Chris

Code Block

DECLARE @TestTable TABLE (ID INT IDENTITY(1, 1), TestString VARCHAR(10))
INSERT INTO @TestTable
VALUES ('TestString')

--This will work as T1 is an integer
SELECT T1, T2
FROM
(SELECT ID AS T1, TestString AS T2
FROM @TestTable) t
ORDER BY CASE WHEN 1 = 1 THEN T1
WHEN 1 != 1 THEN T2
END

--This will fail as T2 cannot be converted to an integer
SELECT T1, T2
FROM
(SELECT ID AS T1, TestString AS T2
FROM @TestTable) t
ORDER BY CASE WHEN 1 != 1 THEN T1
WHEN 1 = 1 THEN T2
END

Frank Kalis

See if this help: http://www.sommarskog.se/dyn-search.html

hunchback

See if this helps.

How do I use a variable in an ORDER BY clause

http://databases.aspfaq.com/database/how-do-i-use-a-variable-in-an-order-by-clause.html

AMB

Order by using @Params

Re: Order by using @Params

Re: Order by using @Params

Re: Order by using @Params

Re: Order by using @Params

Re: Order by using @Params

Re: Order by using @Params