Writing Dynamic SQL

I’m currently working on presentation for Dynamic SQL, and I thought I’d share one of my favorite tricks.  I had actually posted it to USENET earlier, so you can read the whole thread here, complete with some very useful suggestions, but here’s a synopsis:

As you’re probably aware, writing dynamic SQL statements can be a bear, and often involve breaking in and out of a concatenated string like so:

DECLARE @sql nvarchar(MAX)
SELECT @sql = N'SELECT * FROM '+ @DatabaseName
                + '.'+ @SchemaName
                + '.'+ @TableName
                + ' WHERE '+ @ColumnName + ' = ' + @Value
EXEC sp_executesql @sql

(Note that I am NOT endorsing the use of this example, or the cost or benefit of dynamic SQL as a whole; I’m simply showing a method of writing dynamic SQL when needed).

I use a REPLACE statement to avoid dinking around with the extra quotation marks, and thanks to suggestions by Erland and others (in the above thread), I can also mitigate some of the risk of SQL injection, like so:

SET @SQL = N'SELECT *
             FROM |DatabaseName|.|SchemaName|.|TableName|
             WHERE |ColumnName| = @param_value'

SET @SQL = REPLACE(@SQL,'|DatabaseName|',QUOTENAME(@DatabaseName))
SET @SQL = REPLACE(@SQL,'|SchemaName|',QUOTENAME(@SchemaName))
SET @SQL = REPLACE(@SQL,'|TableName|',QUOTENAME(@TableName))
SET @SQL = REPLACE(@SQL,'|ColumnName|',QUOTENAME(@ColumnName))

Happy coding!