Dynamic Sorting Within SQL Stored Procedures

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)

SQL Server dynamic sorting on multiple columns

ROW_NUMBER() OVER
            (
            ORDER BY
            --  string order by
            CASE @SortDirection
                  WHEN 'ASC'  THEN
                    CASE @SortIndex
                      WHEN 1 THEN SKU
                      WHEN 2 THEN BrandName + ',' + ItemName 
                      WHEN 3 THEN ItemName
                    END            
                END ASC,
                CASE @SortDirection
                  WHEN 'DESC' THEN
                    CASE @SortIndex
                      WHEN 1 THEN SKU
                      WHEN 2 THEN BrandName + ',' + ItemName
                      WHEN 3 THEN ItemName
                    END
                END DESC,

Use Brandname + ItemName in the When 2 Clause and to have both fields be used in the sort.

SQL Server dynamic sorting on multiple INT columns

The only way I can think to get around this is to convert to a varchar, then make sure they all have the same amount of characters by adding leading zeros:

WHEN 5 THEN RIGHT('0000000000' + CAST(InStockLocal.Qty AS VARCHAR(10)),10) 
+ RIGHT('0000000000' + CAST(AllStock.Qty AS VARCHAR(10)),10)

I assumed 10 characters would be the max length because that's what you showed in your example.

Dynamic stored procedures Order by Columns Title

I create a SP to do below steps for ordering:

• 1) Insert dynamic SPs result into a #TempTable

• 2) Find my query result columns with in tempdb.sys.[columns] table, because my SP result now stored at temp table.

• 3) Sort Founded columns title and insert that in the string by this pattern:

  [col1], [col2], [col3], ...

• 4) Create a T-SQL this model:

  'SELECT ' + 'sorted columns: [col1], [col2], [col3], ... ' + ' FROM ' + '#temptable'

• 5) Execute that created T-SQL to display sorted SP results.

In test project, my dynamic SP names is: TestDB.dbo.sp_TEST.
Now Sorter SP codes is:

/*
 * Run Just Once Time For Set Configures and Create Linked Server
 */
-- Set Permissions to insert into a temp table
--sp_configure 'Show Advanced Options', 1
--GO
--RECONFIGURE
--GO
--sp_configure 'Ad Hoc Distributed Queries', 1
--GO
--RECONFIGURE
--GO

--EXEC sp_addlinkedserver 
--     @server = 'LOCALSERVER',
--     @srvproduct = '',
--     @provider = 'SQLNCLI',
--     @datasrc = 'Localhost'



DECLARE @tbl     VARCHAR(MAX) = '#SortedColsTempTable',
        @sql     VARCHAR(MAX)

-- If old temp table is exist then clear that
IF OBJECT_ID('tempdb..#SortedColsTempTable') IS NOT NULL
    DROP TABLE #SortedColsTempTable

-- Insert your results into #SortedColsTempTable temp table's
SELECT * INTO #SortedColsTempTable
FROM   OPENQUERY([LOCALSERVER], 'EXEC TestDb.dbo.sp_TEST 1') 

-- Create a string by sorted columns title
SET @sql = (
        SELECT '[' + c.name + '], ' AS [text()]
        FROM   tempdb.sys.[columns] c
               INNER JOIN tempdb.sys.tables t
                    ON  t.[object_id] = c.[object_id]
        WHERE  t.name LIKE '#SortedColsTempTable%'
        ORDER BY
               c.name
               FOR XML PATH('')
    )

SET @sql = 'SELECT ' + LEFT(@sql, LEN(@sql) -1) + ' FROM ' + @tbl

EXEC (@sql)

---

Related Topics