selective filtering, part ii - dynamic sql sorting 1

my previous post on selective filtering dealt with filtering a select statement if a non-null parameter value is passed to your stored procedure without using dynamic sql. however, there will be times when dynamic sql is required. since the 'sp_executesql' stored procedure was included in SQL 2005 and was not removed in SQL 2008 (to my best knowledge), i dare anyone to dispute that (but if you do so successfully i will be simultaneously amazed, humbled and eternally grateful).

for the uninitiated, dynamic sql is the process of building a query on-the-fly based on parameters passed to the stored procedure. an example of when this is required is if your query accepts the name of a column as a parameter (@param1) and you want to sort your query based on this column/parameter. you can't order a query by a constant, null, or a parameter, even if the parameter evaluates to a valid column name. instead, you write something like this:

DECLARE @sSQL nvarchar(4000)

SET @sSQL = 'SELECT col_a FROM table_a '
IF @param1 IS NOT NULL
SET @sSQL = @sSQL + ' ORDER BY ' + @param1

exec sp_executesql @sSQL
GO

assuming @param1 is a valid column name in table_a, the statement will run and return the values of col_a ordered by the @param1 column (in ascending order by default). notice that i checked to make sure @param1 contained some value since you can't order by null and, if i hadn't checked and @param1 was null, @sSQL would be null (since anyting + null = null). sp_executesql won't throw an error if @sSQL is null; it will just say "Command(s) completed successfully." and return nothing.

that does it for the basics of dynamic sql sorting. the reason i named this post 'dynamic sql sorting 1' is because i can think of at least one more complicated case (e.g. using a case statement to select a column to sort by when another column's value is equal to an input parameter) that i don't want to go into right now since this post is already pretty long. good luck and by all means let me know if you have any comments, corrections or questions. später