DEV Community πŸ‘©β€πŸ’»πŸ‘¨β€πŸ’»

DEV Community πŸ‘©β€πŸ’»πŸ‘¨β€πŸ’» is a community of 966,155 amazing developers

We're a place where coders share, stay up-to-date and grow their careers.

Create account Log in
Zohar Peled
Zohar Peled

Posted on

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

Top comments (0)

🌚 Browsing with dark mode makes you a better developer by a factor of exactly 40.

It's a scientific fact.