loading...

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

peledzohar profile image Zohar Peled ・2 min read

(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!

Posted on by:

peledzohar profile

Zohar Peled

@peledzohar

By day, try to work. By night, try to sleep.

Discussion

pic
Editor guide