ORDER BY CLAUSE

  • 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?

  • 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)

    ---

  • 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?

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

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply