use a variable in an ORDER BY clause?

• CooLDBA

  Hall of Fame

  Points: 3301

  More actions

  November 23, 2005 at 12:14 pm

  #95747

  I know I can't use this:

  DECLARE @col VARCHAR(9)

  SET @col = 'firstname'

  SET @col2 = 'surname'

  SET @col3 = 'Address'

  EXEC('SELECT * FROM blat ORDER BY '+@col, @col2, @col3)

  GO

  'coz I read in this article: http://www.aspfaq.com/show.asp?id=2501

  But is there someone had a same req and done it. Just let me know how you did it.

  I want to order by from a three passing parameter.

  Take note: I used order by CASE @col but can't work for me 'coz I can't have order by firstname, firstname.

  Do you know what I mean?

  Thanks!

• Roy Rogers

  SSCertifiable

  Points: 7712

  More actions

  November 23, 2005 at 12:24 pm

  #605531

  The post http://www.aspfaq.com/show.asp?id=2501 also says use CASE in ORDER BY CLAUSE. Dynamic SQL should be last thing to think.

  For multiple columns use

  EXEC('SELECT * FROM blat ORDER BY ' + @col + ', ' + @col2 + ', ' + @col3)

  Regards,
  gova