SQL Server sort by first column in table

Question

exec ('select * from variable_table_name order by @variable') 

I am trying to assemble a dynamic sql element in which you specify the table name and sort column. It would be nice if I could just provide a table name to make sql work.

Is there a way to select the first column of the table to sort if @variable sort is not specified?

Answer 1

You can use:

 ORDER BY 1; 

From the ORDER BY Clause (Transact-SQL) :

order_by_expression - Specifies a column or expression to sort the query result set. The sort column can be specified as an alias of the name or column, or a non-negative integer representing the position of the column in the selection list .

(my emphasis)

Answer 2

This script should do the job

 DECLARE @tbl NVARCHAR(128) DECLARE @col NVARCHAR(128) DECLARE @sql NVARCHAR(MAX) SET @tbl = 'MyTbl' SET @col = '' --enter column name when required IF @col = '' BEGIN SET @sql = 'SELECT * FROM ' + @tbl + ' ORDER BY 1' END ELSE SET @sql = 'SELECT * FROM ' + @tbl + ' ORDER BY ' + @col EXEC(@sql) 

Answer 3

Something like this (like Ive used Adventureworks): -

 DECLARE @SORTCOLUMN varchar(50); SET @SORTCOLUMN = 'OrganizationLevel'; IF @SORTCOLUMN IS NULL EXEC('SELECT * FROM HumanResources.Employee ORDER BY 1') ELSE EXEC('SELECT * FROM HumanResources.Employee ORDER BY ' + @SORTCOLUMN) 

Leave the default "SET @SORTCOLUMN" row for the first column