How to Store SQL Server Sort Order in a Variable

Can I store SQL Server sort order in a variable?

Pass in @OrderBy int, where positive is ASC, negative is DESC, and the actual number is the column to sort by:

SELECT
    dt.yourColumn1
        ,dt.yourColumn2
        ,dt.yourColumn3
        ,CASE 
            WHEN @OrderBy>0 THEN dt.SortBy
            ELSE NULL
         END AS SortByAsc
        ,CASE 
            WHEN @OrderBy<0 THEN dt.SortBy
            ELSE NULL
         END AS SortByDesc
    FROM (SELECT
              yourColumn1
                  ,yourColumn2
                  ,yourColumn3
                  ,CASE
                      WHEN ABS(@OrderBy) = 1 THEN surname
                      WHEN ABS(@OrderBy) = 2 THEN forename
                      WHEN ABS(@OrderBy) = 3 THEN fullName
                      WHEN ABS(@OrderBy) = 4 THEN CONVERT(varchar(10),userId)
                      WHEN ABS(@OrderBy) = 5 THEN CONVERT(varchar(10),MobileNumber
                      WHEN ABS(@OrderBy) = 6 THEN DeviceStatus
                      WHEN ABS(@OrderBy) = 7 THEN LastPosition
                      WHEN ABS(@OrderBy) = 8 THEN CONVERT(varchar(23),LastAlert,121)
                      WHEN ABS(@OrderBy) = 9 THEN CONVERT(varchar(23),LastCommunication,121)
                      WHEN ABS(@OrderBy) =10 THEN CONVERT(varchar(23),LastPreAlert,121)
                      ELSE NULL
                  END AS SortBy
              FROM YourTablesHere
              WHERE X=Y
         ) dt
    ORDER BY SortByAsc ASC, SortByDesc DESC

Just make sure you build a string that sort properly. Notice I used 'YYYY-MM-DD hh:mm:ss.mmm' for the dates and put the numbers into strings. We usually put multiple columns together, so if you sort by surname, forename is used too, etc. Watch out, if you do combine multiple columns you'll need to pad with zeros or spaces.

If you don't want the SortByAsc and SortByDesc columns to be in the result set, wrap the entire thing in a derived table.

Using variables for asc and desc in order by

without Dynamic SQL each option it's clause for example:

ORDER BY
  case when @var1 = 'priority asc' THEN priority END ASC ,
  case when @var1 = 'priority desc' then priority end DESC,
  case when @var2 = 'report_date asc' then report_date end ASC,
  case when @var2 = 'report_date desc' then report_date end DESC

VARIABLE IN ORDER BY IN STORED PROCEDURE

It is not possible to use a variable in order by clause, you need to make a string for your query and then execute it by sp_execute_sql

DECLARE @sql NVARCHAR(MAX)
SET @sql = 'SELECT ... '+
'FROM sys.dm_exec_query_stats AS qs '+
'CROSS APPLY sys.dm_exec_sql_text(qs.SQL_HANDLE) AS st '+
'CROSS APPLY sys.dm_exec_query_plan(qs.plan_handle) AS qp '+
'WHERE '+@where+' LIKE ''%'+@search+'%'''+
'ORDER BY '+@order

EXEC sp_executesql @sql

Order By using a parameter for the column name

You should be able to do something like this:

SELECT *
FROM
    TableName
WHERE
    (Forename LIKE '%' + @SearchValue + '%') OR
    (Surname LIKE '%' + @SearchValue + '%') OR
    (@SearchValue = 'ALL')
ORDER BY 
    CASE @OrderByColumn
    WHEN 1 THEN Forename
    WHEN 2 THEN Surname
    END;

• Assign 1 to @OrderByColumn to sort on Forename.
• Assign 2 to sort on Surname.
• Etc... you can expand this scheme to arbitrary number of columns.

---

Be careful about performance though. These kinds of constructs may interfere with query optimizer's ability to find an optimal execution plan. For example, even if Forename is covered by index, query may still require the full sort instead of just traversing the index in order.

If that is the case, and you can't live with the performance implications, it may be necessary to have a separate version of the query for each possible sort order, complicating things considerably client-side.

Preserving ORDER BY in SELECT INTO

What for?

Point is – data in a table is not ordered. In SQL Server the intrinsic storage order of a table is that of the (if defined) clustered index.

The order in which data is inserted is basically "irrelevant". It is forgotten the moment the data is written into the table.

As such, nothing is gained, even if you get this stuff. If you need an order when dealing with data, you HAVE To put an order by clause on the select that gets it. Anything else is random - i.e. the order you et data is not determined and may change.

So it makes no sense to have a specific order on the insert as you try to achieve.

SQL 101: sets have no order.

Passing dynamic order by in stored procedure

You can use a complicated order by clause. That requires one case for each sort direction and each data type. With this example dataset:

create table t1 (id int, name varchar(50), created date);
insert t1 values 
    (1, 'Chihiro Ogino', '2012-01-01'), 
    (2, 'Spirit of the Kohaku River', '2012-01-03'), 
    (3, 'Yubaba', '2012-01-02');

You could use an order by clause like:

declare @sortColumn varchar(50) = 'created'
declare @sortOrder varchar(50) = 'DESC'

select  *
from    t1
order by
        case
        when @sortOrder <> 'ASC' then 0
        when @sortColumn = 'id' then id
        end ASC
,       case
        when @sortOrder <> 'ASC' then ''
        when @sortColumn = 'name' then name
        end ASC
,       case
        when @sortOrder <> 'ASC' then cast(null as date)
        when @sortColumn = 'created' then created
        end ASC
,       case
        when @sortOrder <> 'DESC' then 0
        when @sortColumn = 'id' then id
        end DESC
,       case
        when @sortOrder <> 'DESC' then ''
        when @sortColumn = 'name' then name
        end DESC
,       case
        when @sortOrder <> 'DESC' then cast(null as date)
        when @sortColumn = 'created' then created
        end DESC

Working example at SQL Fiddle.

Another option is to create the query dynamically, and run it with exec. For example:

declare @sql nvarchar(max)
set @sql = 'select * from YourTable order by ' + @sortColumn + ' ' + @sortDir
exec (@sql)

why does sql server auto sort a table variable's rows?

If you wanted them ordered by the insert order then add an auto-incrementing field your table, and you can then include that in the ORDER BY when you SELECT the data.

SQL Server will otherwise not return your data in a particular order - it may seem to be returning it 'sorted' right now, but that may not be the case in the future.

By the way - the union query itself is actually returning the results ordered differently than they appear in your statement. This is likely the result of using UNION vs. UNION ALL, since union is distinct it likely implies a sort of some type. So, the result you're getting actually IS the insert order.

---

Related Topics