DEV Community

Zohar Peled
Zohar Peled

Posted on

4 3

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!

Image of Datadog

The Essential Toolkit for Front-end Developers

Take a user-centric approach to front-end monitoring that evolves alongside increasingly complex frameworks and single-page applications.

Get The Kit

Top comments (0)

Postmark Image

Speedy emails, satisfied customers

Are delayed transactional emails costing you user satisfaction? Postmark delivers your emails almost instantly, keeping your customers happy and connected.

Sign up