ORDER BY clause - newbie question

Hi,

Is there any way of passing a variable instead of a hard-coded column name in the ORDER BY clause? E.g.

declare @.OrderCol int
set @.OrderCol = 1 select * from tbl_Box order by @.OrderCol

I know the above code won't run. What I need is be able to determine to sort column at run-time so that instead of writing four different stored procedures with hard-coded order by clauses, I could pass the sort column as an extra parameter to a generic stored procedure. Is that possible at all?

Any help will be appreciated.

Cheers,

Vladislav

Hi Vladislav,

Yes, you can. In your scenario, you would:

declare @.s nvarchar(255),
@.c nvarchar(100)

set @.s = 'select * from tblBox order by '
set @.c = '1' --or 2 or 'BoxNumber' etc.

set @.s = @.s + @.c

exec sp_executesql @.s

Cheers

Rob

|||

Hi Rob,

Thanks a lot. This should certainly help. What I was also looking for is be able to create the following stored procedure

MyDB_sp_GetBoxesByCustomerId [param 1] @.CustomerId int, [param 2] @.SortColumn nvarchar(128)

After some data manipulation, this stored procedure would return a resultset sorted based on the input column name. I would use this stored procedure in my .NET application.

Thanks to your advice, I now know I can build an SQL string and, using sp_exectesql, run it in a .NET program, but I was hoping to find a solution to keep all the 'messy' SQL manipulations inside the stored procedure. Do you think this will be possible?

Once again, thanks your your help.

Cheers,

Vladislav

|||

Hi,

Maybe you can use a construction like:

Select * From Table
Order by Case @.xSort
When 1 Then ColumnName1
When 2 Then ColumnName2
When 3 Then ColumnName3
End

The @.xSort would need to be an input parameter to your procedure

---

Best regards Georg
www.l4ndash.com - Log4net Dashboard / Log4net viewer|||

Thanks alot, Georg. This is certainly a better solution.

Regards,

Vladislav

|||If the possible sort columns are not type-compatible, you will need to do this:

...

order by

case @.xSort when 1 then ColumnName1 end,

case @.xSort when 2 then ColumnName2 end,

case @.xSort when 3 then ColumnName3 end

If you don't do this, the CASE statement will raise an exception the

first time you sort by a column containing a value that cannot be

converted to the highest-precedence type of the three columns.

This version will also avoid unnecessary type conversion in the CASE

statement that could lead to a slower-running query, if an index can't

be used as a result.

Steve Kass

Drew University|||

Drew,

Thanks a bunch. I tried the initial version. As you predicted, I got an exception because my query indeed had a column that could not be converted to the first column. With nothing in MSDN, I was just about to rewrite the stored procedure, when I thought I should check out the forum once more.

Once again, thank a lot.

Cheers,

Vladislav