Passing ORDER BY to SQL Server Stored Procedures as a Parameter

Just about anyone who has worked with legacy code, especially when modifying the functionality of compiled assemblies, has run into this problem.  You can't modify the majority of the business code, and inheriting and overriding the code is not feasible due to impact on the system as a whole.

In the case that inspired this post, I needed to populate a simple ASP.NET 1.1 data repeater with data in a specific order, with minimal impact to the existing code.  The existing code did not contain DataView or DataAdapter ADO.NET objects.  Although I could have composed a SQL statement and switched the SqlCommand object to text query mode, I knew that is a bad idea, because:

1. Composed text queries are begging for an injection attack, and introduce a massive security hole
2. Stored procedures are much more efficient, because they are pre-compiled

My solution was to create a stored procedure on SQL Server, that performs the sorting for me.  All I had to do was pass the order by column to a varchar input parameter.  The trouble with using a parameter in an ORDER BY statement is that it simply doesn't work. The following script WILL NOT WORK.

SELECT * FROM Products ORDER BY @orderByColumn

The solution is to use a bit of both worlds.  Pass the column name as a parameter to the stored procedure, and then compose it into a varchar parameter.  Finally, execute the parameter containing the composite query.

DECLARE @orderByColumn    varchar(50) -- (THE INPUT PARAMETER)
        @sql              varchar(255)

-- Be sure to include a space after ORDER BY
SET @sql = 'SELECT * FROM Products ORDER BY ' + @orderByColumn

EXEC(@sql)

Receiving data in the order you need saves you the trouble of adding a DataTable, DataView, DataAdapter, and possibly other objects, and allows you to bind the command results directly to a data repeater.