ORDER BY CLAUSE

• dream coder

  Right there with Babe

  Points: 723

  More actions

  September 23, 2008 at 4:54 am

  #193381

  Hi all,

  I need to have parameter values in ORDER BY CLAUSE.

  I have the following statement which doesnt seem to work:

  DECLARE @Name nvarchar(60)

  ,@Order nvarchar(4)

  SET @Name = 'Name'

  SET @order = 'asc'

  SELECT * FROM tbl_employee

  ORDER BY @Name + '' + @order

  but this doenst seem to work, it doesnt order by anything. I need to be able to order by @Name and have another parameter to set it to either ASC or DESC...

  can u have dyanmic order by's?

  any idea guys?

• SqlUser-529296

  SSCarpal Tunnel

  Points: 4072

  More actions

  September 23, 2008 at 5:13 am

  #875549

  Hi,

  Try this...

  DROP TABLE #Test

  CREATE TABLE #Test (fldName varchar(8))

  INSERT INTO #Test

  SELECT 'Suresh'

  UNION ALL

  SELECT 'Arun'

  UNION ALL

  SELECT 'Kamal'

  UNION ALL

  SELECT 'Bala'

  DECLARE @Name varchar(8), @Order varchar(8),@Sql varchar(50)

  SET @Name = 'fldName'

  SET @Order = 'ASC'

  SET @sql = ''

  SET @sql = 'SELECT * FROM #Test ORDER BY ' + @Name + ' ' + @Order

  EXEC (@Sql)

  ---

• dream coder

  Right there with Babe

  Points: 723

  More actions

  September 23, 2008 at 6:07 am

  #875578

  hi thanks this does work however how would i do wit without use T-SQL syntax ie without the exec @sql, jus run as a select with an order by without the '' etc...is there a way to do it this way?

• arjun.tewari

  Ten Centuries

  Points: 1013

  More actions

  September 23, 2008 at 6:56 am

  #875606

  I am afraid that there is no way out then dynamic SQL. Its not that bad why dont you want to use it?