Using Dynamic Execution to Generate Multiple IF Statements

Q. I want to avoid having to script many conditional (IF) statements that implement sorting and filtering schemes so that I can shorten the amount of code that will have to be maintained. Listing 5, which implements a sorting scheme, shows the kind of statement I want to avoid. To reduce the amount of required code, I've tried using IF statements within SQL statements such as

SELECT LastNameFROM EmployeesORDER BYIF @type = 'a' ASCIF @type = 'd' DESC

But this technique doesn't work and results in errors. How can I sidestep such a huge implementation of IF statements?

A. One way you can shorten your project is by using dynamic execution to generate multiple IF statements, which Listing 6, page 13, shows. This code assumes that if @type is neither 'a' nor 'd', the result should be returned in no particular order. Dynamic execution lets you construct a string containing a valid T-SQL statement and execute the string. The main advantages of dynamic execution are that only one query is constructed and that any part of the query string can be dynamic--table and column names, or even the sort order--as opposed to static SQL. Dynamic execution has its disadvantages. The code is hard to maintain because it isn't very readable, and it carries security concerns as well. If you want to implement this query inside a stored procedure that includes @type as a parameter, the user who executes the stored procedure would need direct SELECT permissions on the Employees table. EXECUTE permissions on a stored procedure aren't sufficient for dynamic execution.

Typically, you should avoid dynamic execution when you have other alternatives. In this case, you can avoid dynamic SQL and still control the sort order by using two separate CASE expressions, as the code in Listing 7, page 13, shows.