Home SQL Server Archive by category "Dynamic SQL" (Page 2)

Category: Dynamic SQL

Getting to Grips with Dynamic SQL: The beginning

Dynamic SQL is a tool that many of us may end up using in the future, or have in the past. Often, however, it’s use can frowned upon because it often can be a sign that what you have really is a design problem, or what is known as an “XY Problem”. Most of the questions I do see are an indication of this, such as “I need a Procedure that accepts a table name as a parameter for an INSERT.” These sorts of requirements are often clear examples, as this likely means you are using objects names to infer…

Continue reading

A CURSOR free version of sp_msforeachdb

Something that we are all keenly aware of is that (generally) the use of a CURSOR in T-SQL is generally bad. A CURSOR is an iterative based method, and these generally perform very poorly in SQL Server, which is far better suited to set-based methods. sp_msforeachdb is an undocumented Special Procedure, so one day it may be removed (unannounced), but it also uses a CURSOR and can be unreliable. Something, like Aaron Bertrand did with sp_foreachdb, I have designed my own with some extra features. The Objects Let’s get to the interesting part first, the Objects. There are 2 parts…

Continue reading

Why I define my Carriage Returns and Line Breaks as variables in Dynamic SQL

A question I’ve been asked on several occasions is “Why use NCHAR(13) + NCHAR(10), or @CRLF in your code, and not use inline new lines in your Dynamic SQL?” So, what someone means is why do I write my dynamic statements like this: DECLARE @SQL nvarchar(MAX), @CRLF nchar(2) = NCHAR(13) + NCHAR(10); SET @SQL = N’SELECT *’ + @CRLF + N’FROM ‘ + QUOTENAME(@TableName) + N’;’; Instead of code like this: DECLARE @SQL nvarchar(MAX); SET @SQL = N’SELECT * FROM ‘ + QUOTENAME(@TableName) + N’;’; I actually have a couple of reasons for this. SQL Server is on Windows and…

Continue reading