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

Category: Dynamic SQL

Getting to Grips with Dynamic SQL: Validating inputs

In the last article I touched on how you would build a dynamic query which is likely, in the end, accepting an input from a user to produce a dynamic statement. These work well but often, for added security, it’s best to also validate the input value prior to executing the statement. This either means that the dynamic SQL isn’t executed at all, or you can choose to return a custom error message. Validating Objects When using dynamic objects, then use you use the sys or INFORMATION_SCHEMA objects to check that the object does intend exist. Personally I prefer the…

Continue reading

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