I was wondering if it was possible to use the ORDER BY clause with a variable... I have a variable called voteAverage and I need the records to descend but I can't seem to successfully put the variable voteAverage into the SQL.... any ideas?

thanks in advance.

Dear Simon,
If that variable refers to a field - post your query.

Regards,
Brent

It does not refer to a field.. it refers to a variable I declared in the main code (e.g. dim voteaverage)

Hi Simon,

<BLOCKQUOTE id=quote><font size=1 face="Verdana, Arial, Helvetica" id=quote>quote:<hr height=1 noshade id=quote> I was wondering if it was possible to use the ORDER BY clause with a variable... I have a variable called voteAverage and I need the records to descend but I can't seem to successfully put the variable voteAverage into the SQL.... any ideas?<hr height=1 noshade id=quote></BLOCKQUOTE id=quote></font id=quote><font face="Verdana, Arial, Helvetica" size=2 id=quote>

Yes it is..... you'll need to modify this code to fit your needs. Try this for size, I've made this as generic as possible.

CREATE PROCEDURE spMySortOrder

@SortOrder as varchar(30), --make this long enough to accept the column name
@AscDesc as varchar(4) --gives you the choice of ASC or DESC, could be useful on long pages.

AS

declare @str varchar(800)

--now set the Select statement to a string value

SET @str = 'SELECT my_col_1, my_col_2, my_col_3, my_col_4
FROM my_table
WHERE (my_col_1 = 1)' + --or whatever where clause you like
' ORDER BY ' + @SortOrder + ' ' + @AscDesc

exec (@str)

Digga

Sharing Knowledge Saves Valuable Time!!!