parameter in Order By clause? 1

[chris123321]

A user has a drop-down list in which he can decide how to Group By a report. And the Group-By of the report depends on the Order By clause of the stored procedure. So for each of the different selections in the dropdownlist, the report will look different.

The best solution I found is using a case statement from:

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

DECLARE @col VARCHAR(9) 
SET @col = 'firstname' 
 
IF @col IN ('firstname', 'email') 
    SELECT * FROM blat 
        ORDER BY CASE @col 
            WHEN 'firstname' THEN firstname 
            WHEN 'email' THEN email 
        END 
ELSE 
    SELECT * FROM blat 
GO

Any other suggestions?

 

[monksnake]

What you have for your posted code looks fine. Since you are pulling the value of the parameter from a drop-down, why do you need to check with an IF statement on the value of the parameter though?
Assuming you are ordering by any value in the drop-down box, no IF is needed.

But that is an assumption.



<.

 

[chris123321]

Sorry, I just used the example from that site..

You're right, in the code I write there will be no if statement.

 

[chris123321]

Whats a good site to learn dynamic sql including the pros and cons of using it?

 

[rac2]

This is a good site for that.

Cons-
Must be compiled every time you use it.
Pros-
No alternative.

In other words, dont use dynamic sql if you can find a way to avoid it.

 

[rac2]

BTW, a CASE expression is not what I mean by -dynamic-sql-. I mean building strings and then EXECUTEing them.

 

[ilyad]

Yes, it is the best solution. See also

http://www.sqlmag.com/Articles/Index.cfm?ArticleID=16495

 

[chris123321]

Ilyad - the issue with Listing1 is that there are x amount of ways that a developer can think of how the user would like the data sorted. If the user decides that he wants to sort by another way then the stored procedure needs to be rewritten.

So I'm wondering how dynamic-sql can come into play in this scenario.

Thanks.

 

[ilyad]

What is the problem of writing dynamic sql?

 

[chris123321]

From this site

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

I'm trying to figure out how to have multiple columns in an order-by clause.

For example,

SELECT * FROM Products
ORDER BY
CASE WHEN @OutputFormat = 'ProductId'
THEN ProductId, ProductName, ProductDescription
ELSE @OutputFormat = 'ProductName'
THEN ProductName, ProductId, ProductDescription
END

When I try this I get Incorrect syntax near ',' pointing to THEN ProductId, ProductName, ProductDescription

Thanks for the help.

 

[gmmastros]

Try this...

SELECT * FROM Products
ORDER BY 
CASE WHEN @OutputFormat = 'ProductId'
THEN ProductId
Else ProductName
END,
CASE WHEN @OutputFormat = 'ProductId'
THEN ProductName
ELSE ProductId
END,
ProductDescription

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[gmmastros]

There's another problem. Sorry.

When using case/when, it is important for each output to have the same data type. Since you are using ProductId and ProductName, you may run in to this problem. Here's how to fix it.

[COLOR=blue]Declare[/color] @Products [COLOR=blue]Table[/color](ProductId [COLOR=blue]Int[/color], ProductName [COLOR=blue]varchar[/color](20), ProductDescription [COLOR=blue]VarChar[/color](20))

[COLOR=blue]Insert[/color] [COLOR=blue]into[/color] @Products [COLOR=blue]values[/color](1,[COLOR=red]'hat'[/color],[COLOR=red]'Big hat'[/color])
[COLOR=blue]Insert[/color] [COLOR=blue]into[/color] @Products [COLOR=blue]values[/color](2,[COLOR=red]'Coat'[/color],[COLOR=red]'Wind Breaker'[/color])
[COLOR=blue]Insert[/color] [COLOR=blue]into[/color] @Products [COLOR=blue]values[/color](10,[COLOR=red]'Belt'[/color],[COLOR=red]'leather'[/color])

[COLOR=blue]Declare[/color] @OutputFormat [COLOR=blue]VarChar[/color](10)
[COLOR=blue]Set[/color] @OutputFormat = [COLOR=red]'ProductId'[/color]

[COLOR=blue]SELECT[/color] * [COLOR=blue]FROM[/color] @Products
[COLOR=blue]ORDER[/color] [COLOR=blue]BY[/color] 
[COLOR=blue]CASE[/color] [COLOR=blue]WHEN[/color] @OutputFormat = [COLOR=red]'ProductId'[/color]
[COLOR=blue]THEN[/color] [COLOR=#FF00FF]Right[/color]([COLOR=#FF00FF]Replicate[/color]([COLOR=red]'0'[/color], 20) + [COLOR=#FF00FF]Convert[/color]([COLOR=blue]VarChar[/color](20), ProductId), 20)
[COLOR=blue]Else[/color] ProductName
[COLOR=blue]END[/color],
[COLOR=blue]CASE[/color] [COLOR=blue]WHEN[/color] @OutputFormat = [COLOR=red]'ProductId'[/color]
[COLOR=blue]THEN[/color] ProductName
[COLOR=blue]ELSE[/color] [COLOR=#FF00FF]Right[/color]([COLOR=#FF00FF]Replicate[/color]([COLOR=red]'0'[/color], 20) + [COLOR=#FF00FF]Convert[/color]([COLOR=blue]VarChar[/color](20), ProductId), 20)
[COLOR=blue]END[/color],
ProductDescription

Notice how I convert the integer to a varchar. But more importantly, notice how I pad the numbers with zeros. With the zero padding, numbers will sort properly. And, by converting the integer column to varchar, it allows the sorting no matter what.

Does this make sense?

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[chris123321]

I modified the query to this:

DECLARE @OutputFormat nvarchar(100)
SET @OutputFormat = 'ProductName'


SELECT * FROM Products
ORDER BY SupplierId,
CASE @OutputFormat WHEN 'ProductName'
THEN ProductName
ELSE QuantityPerUnit
END, ProductId, UnitPrice

and it works. But in the 'real' query that I'm working on I'm getting errors as invalid column names, which are in the case by statement. Is that because the two column names are aliased in the select clause?

Thanks.

 

[chris123321]

Sorry, George..

Didnt see the other post.

 

[gmmastros]

Is it working now?

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[chris123321]

The example query works...

But in the 'real' query I'm getting invalid column names in the case statement of the Order By clause. I thought the error was due to aliasing, but when I add fields that aren't aliased to the case statement I get the error: ORDER BY items must appear in the select list if SELECT DISTINCT is specified.

Trying to figure out other ways to debug this problem..

 

[gmmastros]

You could try using a derived table. Ex:

Select Col1, Col2, Col3
From   ([!]
       Select Col1, Col2, Col3
       From   SomeTable
       Where  ....
       [/!]
       ) as A
ORDER BY 
CASE WHEN @OutputFormat = 'ProductId'
THEN Right(Replicate('0', 20) + Convert(VarChar(20), ProductId), 20)
Else ProductName
END,
CASE WHEN @OutputFormat = 'ProductId'
THEN ProductName
ELSE Right(Replicate('0', 20) + Convert(VarChar(20), ProductId), 20)
END,
ProductDescription

That red part would by your original query.

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[chris123321]

still stumped...

Here is the order by query that I'm working with:

CASE @Outputformat WHEN 'Test1' THEN  LInitials
                   ELSE Orig END, 
ControlID, LPOrder, Priority, SumAmount desc, NName

LInitials and Orig are fields that are aliased in the select clause. The sproc compiles without any errors, but when it is executed the error is Invalid column LInitials and Invalid column Orig.

George - I'm not sure if I can do the derived table as there are many fields and subqueries in the select clause.

I'm not sure why the error invalid column name is coming as both fields are in the select query.

Any suggestions on how to debug?

 

[gmmastros]

If you use a derived table, you can use the column names. Otherwise, you need to reproduce the calculation in the order by. I see no reason why you wouldn't be able to use a derived table. I urge you to try it.


-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[chris123321]

I see what you mean, George...

By using the derived table, I would eliminate having aliases in the case statement of the order by clause. Unfortunately, when I tried using the derived table, there were errors with the aliases in the from and inner join table in the sub query stating that the aliases could not be found.

Then I tried changing the view that the two fields in the case statement of the order by are coming from so there would be no aliasing. But then I had to add an udf to the view table, which caused the stored procedure to work, but the executing of the sproc increased by 23 seconds. Is it not recommended to use udfs in views?

I'll try to figure out why the derived table isn't working.

Hopefully this makes sense. Thanks.