DEV Community

loading...

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

Zohar Peled
By day, try to work. By night, try to sleep.
・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
Enter fullscreen mode Exit fullscreen mode
  • 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
Enter fullscreen mode Exit fullscreen mode
  • 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;
Enter fullscreen mode Exit fullscreen mode
  • Never concatenate parameters into your dynamic SQL string.
set @sql = 'select * from table where x = '+ @x 
Enter fullscreen mode Exit fullscreen mode

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

Discussion (0)