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)
 Avoid dynamic sql in simple situations

Author  Topic 

beyonder422
Posting Yak Master

124 Posts

Posted - 2006-02-03 : 15:57:44
I want to avoid dynamic SQL as much as possible, so for example, in a simple dynamic "order by" situation how can I get around this?

SET @strSQL = 'SELECT column1, column2, column3 from table1 order by ' + @order_by

EXEC (@strSQL)

I know I can do an "if" for every column,

If @order_by = 'column1'
SELECT column1, column2, column3 from table1 order by column1
If @order_by = 'column2'
SELECT column1, column2, column3 from table1 order by column2
etc.

but is there a simpler way/trick?


www.beyonder422.com

ackweb
Yak Posting Veteran

54 Posts

Posted - 2006-02-03 : 16:16:54
You can use a CASE statement as follows:

SELECT column1, column2, column3 from table1

ORDER BY (CASE WHEN @order_by = 'column1' THEN column1 ELSE (CASE WHEN @order_by = 'column2' THEN column2 ELSE column3 END) END)
Go to Top of Page

beyonder422
Posting Yak Master

124 Posts

Posted - 2006-02-03 : 16:35:58
good idea, didn't think about that.


Thanx

www.beyonder422.com
Go to Top of Page
   

- Advertisement -