SQL Question.... Experts Only!!!! 5

[monksnake]

Hello gurus, I have a question regarding a situation.

I have a query that was originally build dynamically using ASP. The ORDER BY clause can 4 different "values", based on a value in an ASP variable.

Here is my question.

What is the best way of going about putting these different scenarios of ORDER BY clauses within my SP?? Or perhaps, should I not even use a SP and possibly use a view instead?

If SP is the right way, should I make 4 different SP, one for each different ORDER BY clause or make 1 SP with each query separated within IF blocks?

If I don't have enough information for you, please let me know.

<.

 

[SQLDenis]

If you don't want to deal with 4 if blocks or worst 4 procs
lookup sp_executesql in BOL or better yet read

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

I would probably do the 4 if statements...what if tomorrow you can sort it 8 different ways and the aslo want paging? then I would look at sp_executesql



Denis The SQL Menace
--------------------
SQL Server Code,Tips and Tricks, Performance Tuning
SQLBlog.com, Google Interview Questions

 

[AlexCuse]

Experts only? Where is the all caps? And URGENT!!!!

Maybe you can pass in a parameter telling it how to sort?

Consider this:

declare @t table (i int, k int)


declare @i int
set @i = 0

while @i < 100
begin
insert @t
select @i, 100 - @i

select @i = @i + 1
end


--this is your parameter, used in a case to determine what to order by
declare @o int
set @o = 1

select * from @t order by case when @o = 1 then i else k end

set @o = 0

select * from @t order by case when @o = 1 then i else k end

Hope it helps,

Alex

Ignorance of certain subjects is a great part of wisdom

 

[monksnake]

Denis, I was aware of the executesql, but I was trying terribly to avoid any dynamic SQL.

Since you would probably do the 4 if statements, then I will, adding the extra sortability wouldn't be any problem to deal with.

Thanks for the advice.

Oh yeah I almost forgot, I thought I said experts only!!!!!


BTW, the "Experts Only" was just a joke.



<.

 

[kaht]

monsnake, don't use dynamic sql - I would suggest you do whatever sqldenis suggests

and for that I deserve a star

-kaht

Lisa, if you don't like your job you don't strike. You just go in every day and do it really half-assed. That's the American way. - Homer Simpson

http://www.silkypups.com

 

[SQLDenis]

Alex suggestion is also good, it only becomes a mess when you have to sort by more than 1 column and also desc or asc

Denis The SQL Menace
--------------------
SQL Server Code,Tips and Tricks, Performance Tuning
SQLBlog.com, Google Interview Questions

 

[monksnake]

Alex, I did try that before I posted (case with order by), and I must've made a stupid syntax error cause I didn't get it to work.

I see that it does work, and that's the way I'll go about it. Thank you all for your help.

<.

 

[monksnake]

kaht, you may just get a star from monsnake.

<.

 

[AlexCuse]

This will trick an integer column to sort descending.

select @o = 2

select * from @t order by
case @o when 1 then i
when 2 then -1 * i
else k end

For a date column to sort descending, I think you could sort by something like this in your case:

datediff(day, dateCol, 0)

(it returns a 'higher' negative number for later dates)

varchar, I am stuck on though.

Ignorance of certain subjects is a great part of wisdom

 

[monksnake]

Turns out the case really only works for 1 column (I assume you knew that). So I'm reverting back to the IF blocks.

<.

 

[monksnake]

?????

<.

 

[SQLDenis]

¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿
????????????????????????????

Denis The SQL Menace
--------------------
SQL Server Code,Tips and Tricks, Performance Tuning
SQLBlog.com, Google Interview Questions

 

[ESquared]

Be aware that CASE statements in the ORDER BY clause fail for SELECT statements using the syntax

SELECT @Variable = @Variable + ColumnExpression
FROM Table
WHERE Blah
ORDER BY
CASE Expression WHEN blah END

The @Variable won't have the right data in it when you're done. Take out the order by clause, and it works fine.

[COLOR=#aa88aa black]Cum catapultae proscriptae erunt tum soli proscript catapultas habebunt.[/color]

 

[ESquared]

oh, and...

ORDER by a column name that doesn't involve the CASE statement and @Variable will have the right value in it. Maybe some more complex expressions will work, too, but once you use the CASE (perhaps with the @Variable in the CASE's Expression, I don't remember) it blows up.

This is also for SQL Server 2000, ymmv on 2005.

[COLOR=#aa88aa black]Cum catapultae proscriptae erunt tum soli proscript catapultas habebunt.[/color]