Dynamic OrderBy Statement in Stored Procedure 

I am trying to pass variable which would define the sort order. 

Now how did this question araise as we all thaught its simple just pass a variable and create the statement. 

This is what I was trying to do. 

CREATE PROCEDURE sp_test1
	-- Add the parameters for the stored procedure here
	@SortField varchar(10) = 'USERNAME' 
AS


BEGIN
	SELECT * FROM USERS order by @SortField asc
END
GO

But I get error. 

Msg 1008, Level 16, State 1, Procedure sp_test1, Line 11
The SELECT item identified by the ORDER BY number 1 contains a variable as part of the expression identifying a column position. Variables are only allowed when ordering by an expression referencing a column name.

Unfortunately U cannot just pass a variable and get it work. 

Solution is to dynamically construct a string that contains the sql statement we want to execute. 

CREATE PROCEDURE sp_test2
	-- Add the parameters for the stored procedure here
	@SortField varchar(10) = 'USERNAME' 
AS
DECLARE @SQLGeneratedString varchar(255)
BEGIN
	SELECT @SQLGeneratedString = 'SELECT * FROM USERS  order by ' + @SortField + ' asc'
	EXEC(@SQLGeneratedString)
END
GO

I have create a variable @SQLGeneratedString and used string operations to genearte the required ouput string withe variable
exec executed the geneatated sql string.