There's a lot to be said for readability in code. Whenever you're writing code you should be thinking about readability. Performance is always important, and obviously you want the code to be functionally correct, but if it's not readable and maintainable you might as well not even write it.

Dynamic SQL

Dynamic SQL can be challenging to read and challenging to write. I'm not sure I've ever met a developer that likes to maintain someone else's dynamic SQL. But there is something you can do about it, and it might even make dynamic SQL a little bit fun.

Here is an example of some dynamic SQL code:

DECLARE @SQL VARCHAR(MAX)
DECLARE @ProductID INT = 492
DECLARE @MinQuantity INT = 2
DECLARE @MaxQuantity INT = 15

SET @SQL =
'SELECT Product.Name, Product.ProductNumber, ' + CHAR(13) +
' ProductInventory.LocationID, ' + CHAR(13) +
' ProductInventory.Quantity, ' + CHAR(13) +
'''' + CONVERT(VARCHAR(32),GETDATE(),121) + ''' AS ReportDate ' + CHAR(13) +
'FROM Production.Product ' + CHAR(13) +
'JOIN Production.ProductInventory ' + CHAR(13) +
' ON Product.ProductID = ProductInventory.ProductID ' + CHAR(13) +
'WHERE 1=1 ' + CHAR(13) +
ISNULL(' AND Product.ProductID = ' + CAST(@ProductID AS VARCHAR(512) ) + CHAR(13),'') +
ISNULL(' AND ProductInventory.Quantity >= ' + CAST( @MinQuantity AS VARCHAR(512) ) + CHAR(13), '') +
ISNULL(' AND ProductInventory.Quantity <= ' + CAST( @MaxQuantity AS VARCHAR(512) ) + CHAR(13), '')

EXEC(@SQL)

This isn't likely on your list of queries to run in the future, but it illustrates a point. Dynamic SQL can be ugly. Now, if we follow a few simple formatting guidelines, and add a few instances of REPLACE, we get something much cleaner:

DECLARE @SQL VARCHAR(MAX)
DECLARE @ProductID INT = 492
DECLARE @MinQuantity INT = 2
DECLARE @MaxQuantity INT = 15

SET @SQL =
REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(
--=========================================================
'
SELECT
    Product.Name,
    Product.ProductNumber,
    ProductInventory.LocationID,
    ProductInventory.Quantity,
    "{{"{{Date"}}}}" AS ReportDate --<<-- We use a double-quote
FROM
    Production.Product
    JOIN Production.ProductInventory
        ON Product.ProductID = ProductInventory.ProductID
WHERE 1=1
    {{"{{ProductIDCondition"}}}}
    {{"{{QuantityGTCondition"}}}}
    {{"{{QuantityLTCondition"}}}}
'
--=========================================================
,'{{"{{ProductIDCondition"}}}}',ISNULL(' AND Product.ProductID = ' + CAST(@ProductID AS VARCHAR(512) ),''))
,'{{"{{QuantityGTCondition"}}}}',ISNULL(' AND ProductInventory.Quantity >= ' + CAST( @MinQuantity AS VARCHAR(512) ), ''))
,'{{"{{QuantityLTCondition"}}}}',ISNULL(' AND ProductInventory.Quantity <= ' + CAST( @MaxQuantity AS VARCHAR(512) ), ''))
,'{{"{{Date"}}}}',CONVERT(VARCHAR(32),GETDATE(),121))
,'"','''') --<-- Replace double-quote with a single-quote.

EXEC(@SQL)

What We Did Here

  1. Use a single block of SQL with place holders
  2. Use double-braced place-holders:
    1. Field names/expressions {{fieldName}}
    2. Variables/parameters {{@variableName}}
  3. Use double quotes in places you would normally use a single quote, then replace at the end. This can make things much easier to look at.

The example above is doing a simple SELECT from AdventureWorks, which may not be the best use case for this ( Read about why you should be using sp_executesql here: The Curse and Blessings of Dynamic SQL ), but it can be great for maintenance/DBA scripts. I am currently working on a method to script out job creation/modifications using TSQL (blog post coming), here is an example where this formatting really adds clarity to the code:

SELECT
REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(
REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(
--=========================================================
'
EXEC msdb.dbo.sp_update_schedule @schedule_id={{"{{scheduleID"}}}},
        @enabled={{"{{enabled"}}}},
        @freq_type={{"{{f_type"}}}},
        @freq_interval={{"{{f_interval"}}}},
        @freq_subday_type={{"{{f_subday_type"}}}},
        @freq_subday_interval={{"{{f_subday_interval"}}}},
        @freq_relative_interval={{"{{f_relative_interval"}}}},
        @freq_recurrence_factor={{"{{f_rec_factor"}}}},
        @active_start_date={{"{{a_start_date"}}}},
        @active_end_date={{"{{a_end_date"}}}},
        @active_start_time={{"{{a_start_time"}}}},
        @active_end_time={{"{{a_end_time"}}}};
'
--=========================================================
,'{{"{{scheduleID"}}}}',schedule_id)
,'{{"{{enabled"}}}}',enabled)
,'{{"{{f_type"}}}}',freq_type)
,'{{"{{f_interval"}}}}',freq_interval)
,'{{"{{f_subday_type"}}}}',freq_subday_type)
,'{{"{{f_subday_interval"}}}}',freq_subday_interval)
,'{{"{{f_relative_interval"}}}}',freq_relative_interval)
,'{{"{{f_rec_factor"}}}}',freq_recurrence_factor)
,'{{"{{a_start_date"}}}}',active_start_date)
,'{{"{{a_end_date"}}}}',active_end_date)
,'{{"{{a_start_time"}}}}',active_start_time)
,'{{"{{a_end_time"}}}}',active_end_time)
FROM
    msdb.dbo.sysschedules
WHERE
    name = 'MyTestSchedule';

Conclusion

Once you start using this format, it's hard to do it any other way. I was introduced to a version of this by the great Jeff Moden ( See him @ SQLServerCentral ) and I haven't looked back since. Not only does this method make your code easier to read, it can also minimize the amount of time you spend hunting down unclosed single quotes, missing spaces, and all the other stuff that makes dynamic SQL so much "fun" to work with.