CODEWITHSUNDEEP
sql-serverstored-proceduresdynamicsql-order-by
Behzad
Behzad

Reputation: 3580

Dynamic stored procedures Order by Columns Title

I want to sort one dynamic stored procedure's results by that columns title.

For example my stored procedure returns this table:

╔══════╦════════╦════╦══════╦═════════════╦═════════════╦════════╗
║ name ║ family ║ id ║ type ║ description ║ create_date ║ row_no ║
╚══════╩════════╩════╩══════╩═════════════╩═════════════╩════════╝

Now, I want to create a stored procedure to execute that dynamic stored procedure, but this time get a results ordered by columns title, like this:

╔═════════════╦═════════════╦════════╦════╦══════╦════════╦══════╗
║ create_date ║ description ║ family ║ id ║ name ║ row_no ║ type ║
╚═════════════╩═════════════╩════════╩════╩══════╩════════╩══════╝

I founded this question answer, but I not sure my way is the correct or easy way!

Can I implement this matter in easy than below answer ?

EDIT:

My answer is not work for columns by XML data types! Because OPENQUERY is not support that.

Upvotes: 0

Views: 78

Answers (2)

Giau Huynh
Giau Huynh

Reputation: 300

Here is my example, hope this help:

CREATE TABLE [dbo].[Test](
    [text1] [nvarchar](500) NULL,
    [text4] [nvarchar](500) NULL,
    [text3] [nchar](10) NULL
) ON [PRIMARY]

GO
create proc sp_TableOrderBy 
@tableName varchar(100)
as
declare @sql nvarchar(max)
declare @tableColOrderBy nvarchar(max)
SELECT @tableColOrderBy = COALESCE(@tableColOrderBy + ', ', '') + Name
FROM sys.columns
WHERE object_id = OBJECT_ID(@tableName)
order by name
set @sql = 'select ' + @tableColOrderBy + ' from ' + @tableName
execute sp_executesql @sql

exec sp_TableOrderBy 'Test'

Upvotes: 2

Behzad
Behzad

Reputation: 3580

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)

Upvotes: 0

Related Questions