Using a variable in Order By clause

[hystericalmarks]

Is it at all possible to use a variable in an Order By clause?&nbsp;&nbsp;I need a stored procedure that would select records in a table and order them by different columns depending on the @Orderby parameter passed.&nbsp;&nbsp;Obviously, the following syntax does not work:<br><br>Create Procedure sp_Test<br>@Orderby varchar(30)<br>As<br>Select *<br>From Test<br>Order by @Orderby<br><br>The only other option I've considered is using 'If' statements for each possible Order by.&nbsp;&nbsp;Anyone else have any ideas?<br>Thanks for you help.<br><br>

 

[Invicta]

You can almost do exactly as you ask:<br><br>CREATE PROC sp_test<br>&nbsp;&nbsp;&nbsp;@Orderby VARCHAR(30)<br>AS<br>&nbsp;&nbsp;&nbsp;DECLARE @CommandText VARCHAR(100)<br>&nbsp;&nbsp;&nbsp;@CommandText = 'SELECT * FROM test ORDER BY ' + @Orderby<br>&nbsp;&nbsp;&nbsp;EXEC (@CommandText)<br><br><br>In this example you pass the order as a parameter.&nbsp;&nbsp;The sp then creates a string, including your parameter, and executes it.&nbsp;&nbsp;Simple eh?<br><br>Incidentally, @CommandText isn't a special variable or keyword - it's just the name of my variable.<br>