The do’s and don’ts of dynamic SQL for SQL Server 

(This is the short version (though it includes all the bullets). A longer version, including some text explanations and links is posted on my blog)

The Do's

• Always white-list your identifiers.
  This can be easily done using system tables or views such as sys.Tables + sys.Column or information_Schema.Column.

• Always use sysname as the data type for identifiers.
  sysname is a built in data type that is equivalent to nvarchar(128) but it’s non-nullable. SQL Server use that data type internally for all objects names.

• Always use exec sp_executeSql to execute your dynamic SQL (unless it needs no parameters which is a rare case).
  

set @sql = N'select col from '+ quotename(@table) N'+ where otherCol = @value'; 

exec sp_executeSql @sql, N'@value varchar(100)', @value

• Always use parameters for data.

set @sql = N'select col from '+ quotename(@table) N'+ where otherCol = @value'; 
-- @table should already be white-listed before you execute the query, so that's O.K

• Always wrap identifiers with quotename. othereise, your statement will break as soon as it contains an identifier with a white space (or one of many other “special” chars).

The Don'ts

• Never pass SQL commands or clauses in parameters.

set @placeholder1 = 'select a, b, c';
set @placeholder2 = 'where x = y';
set @sql = @placeholder1 + 'from tableName '+ @placeholder2;

• Never concatenate parameters into your dynamic SQL string.

set @sql = 'select * from table where x = '+ @x 

Did I leave anything out? If you think I did, please leave a comment so I can improve my post. Thanks!