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!
December 3, 2008
· 
stuart · 
One Comment
Posted in: SQL
One Response
Another trick I use with dynamic SQL is to use a different variable naming scheme so I can figure out where the problem is when I’m testing hundreds of lines of dynamic T-SQL. Typically, I use camel casing, so when I write my dynamic SQL, I use all lowercase with underscores to separate words. Works like a charm.
Leave a Reply