ORDER BY clause - newbie question

Hot Topic

Help! Can I control the recursion times of a recursive CTE?
SQL Server pricing
SQL Express and Bulk Insert
Web Application sample for Data Mining using Association Rules
Unable to build the solution of Stock sample
Transfer initial snapshots using a removable disk
Push Merge snapshot fails with Data is Null message.
How do I save a Stored Procedure in SSMSE?
Can't run reports in Report Builder
Unable to connect to Developer Edition

SQL Server

An attempt to attach an auto-named database ....\aspnetdb.mdf failed
Does SSIS have these features????
SQL Server 2005 Transaction Log
Table visibility setting affects page breaks on groups. Is this a bug?
How to enable remote errors for Reporting Services 2005
Stuck between "Failed to generate user instance" and "An attempt to attach...failed"
sp_helprotect
Can OWC 11 work with SSAS 2005 cubes?
How to get a reference to output buffers in a ProcessInput()
Unpivot Transformation Null Values

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

Answer this question

ORDER BY clause - newbie question

  • FireSon

    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

  • visent

    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

  • marouane

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

    Regards,

    Vladislav

  • Joseph D.L.

    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
     

  • GEO_Barrett

    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

  • mitr_ar

    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
  • ORDER BY clause - newbie question
Answer this question