Hi everybody. I’m writing today about a problem that plagues severly the sharing of solutions using dynamic T-SQL code in the SQL community. It is most of the time unreadable! Since the introduction of nvarchar(max) with SQL2005 I developed many solutions using dynamic SQL code, and I found a great technique to make it readable and maintainable.
Some hints :
- I define a string containing the T-SQL template of the query with tokens to be replaced. Tokens are written as XML opening tags.
- I use the replace statement to replace them (which replace all tags with the same name at once).
- It help also solves easily the problem of quote doublings.
- Few people know that a T-SQL string can start on a line and end on another. This allows to have a formatted query structure.
They are few other tricks about this. I wrote an article in January edition of SQL Server magazine in the reader to reader section on page 7 for those who have the printed edition. On the Web, go to www.sqlmag.com and search for instant doc ID 141080.
The sample is built around two interesting stored procedures: ComparesRows which is able to compare very quickly rows between tables or queries on the the same or different databases on the same or different instance. This demo procedure shows the power of T-SQL dynamic code in a very limited number of lines. The is a powerful example of how who can do powerful T-SQL dynamic coding while retaining it readabiliy. Another procedure (a support procedure) prints dynamic query generated beyond the 8092 limit imposed by management studio. This is useful for debugging purposes to see the generated query.