Hip Hop Coding

I’ve never used Dynamic SQL before yesterday.

If you used to, still do, or think you will use it, the article The Curse and Blessings of Dynamic SQL seems to be an excellent write-up on some important aspects. Including defending against dreaded SQL injection attacks.

On help forums where someone responds “Use Dynamic SQL!”, there’s a good chance there will an example like the following:


DECLARE @SQL nvarchar(2000)
SET @SQL = 'SELECT * FROM TableName WHERE Server = ''' + @ServerID + ''''
EXEC (@SQL)


However, simply using EXEC is not a very good practice. Instead sp_executesql can be used to allow a more secure parameterized query:


DECLARE @SQL nvarchar(2000)
SET @SQL = 'SELECT * FROM TableName WHERE Server = @ServerID'
EXEC sp_executesql @sql, N'@ServerID nvarchar(100)', @TableName


For some reason, the syntax created a mental image of a du-rag clad Darius N’ServerID rapping about the virtues of Dynamic SQL.

October 20th, 2009 5 comments
Posted by Donnie Filed under Computer-fu