The Art Of The SQL Server Stored Procedure: Dynamic SQL

Time Well Spent

---

I’ve spent a lot of time on this blog telling you different reasons why you should, when you should, and how you should use dynamic SQL.

In this post, I’m going to go into some of the finer points of how I approach dynamic SQL to avoid issues — not performance issues — more procedural issues.

Things you should use to protect yourself from wonky object names, string truncation, object identification, and more.

There are many things that I wish Microsoft would do with dynamic SQL to make working with it easier and safer. For one, there should be a specific data type for it with built-in safe guards against SQL injection. Sort of like a template that would make parameter substitution a lot easier, and allow queries in strings to be written without a ton of escape quotes so that they’d be easier to debug and troubleshoot.

It’s a dream that will never come true, but hey we got ledger tables that will eat up plenty of disk space in Azure, so that’s just great.

Quote And Quote Alike

---

The first and most obvious rule I have with dynamic SQL is that if I need to accept object names, I’m using QUOTENAME.

That goes for any object. Server, database, schema, table, view, procedure, function, you get the idea.

Why? Because developers don’t always do nice things when they name things. The most common one that I see is putting spaces in names, but over the years I’ve run into unprintable characters, brackets, symbols like @, #, $, and &, and… unicode characters.

If you look in any of the stored procedures I write, you’ll see how heavily I use QUOTENAME to save myself headaches later.

Now, you could write your own brackets into strings, but they don’t offer the same level of protection against SQL injection.

One thing I will say is smart to do is to keep two copies of anything you’re quoting in the name of. One to use in the dynamic SQL, and one to use for other information you may need to retrieve about the thing you’ve quoted.

As an example, if you accept a database name as a parameter, and then you add quotes to it, it’ll be awfully hard to find any information about it in sys.databases, because database_name will not equal [database_name]

There you go. Your mind is blown.

Always Unicode

---

Whenever I see parameters or variables created to hold dynamic SQL with varchar (and not nvarchar) as the type, I get real nervous.

Why? Because I know some lazy bones is about to concatenate a bunch of user input into their strings and then:

EXEC (@sql);

And that is unsafe, my friends. Very unsafe. This where unfunny people will tell an unfunny joke about Bobby Tables.

When you use sp_executesql to write and run the safe, parameterized dynamic SQL that you should, it expects unicode inputs for both the query that you execute.

DECLARE
    @s varchar(MAX) = 'SELECT d.* FROM sys.databases AS d WHERE d.database_id = @d;',
    @p varchar(MAX) = '@d integer',
    @d integer = 1;

EXEC sys.sp_executesql
    @s,
    @p,
    @d;

This will fail with the error:

Msg 214, Level 16, State 2, Procedure sys.sp_executesql, Line 1 [Batch Start Line 0]

Procedure expects parameter ‘@statement’ of type ‘ntext/nchar/nvarchar’.

And then with the error

Msg 214, Level 16, State 3, Procedure sys.sp_executesql, Line 1 [Batch Start Line 0]

Procedure expects parameter ‘@params’ of type ‘ntext/nchar/nvarchar’.

Because we did not follow instructions.

This, however, will run wonderfully, and keep us safe from unfunny jokes about Bobby Tables.

DECLARE
    @s nvarchar(MAX) = N'SELECT d.* FROM sys.databases AS d WHERE d.database_id = @d;',
    @p nvarchar(MAX) = N'@d integer',
    @d integer = 1;

EXEC sys.sp_executesql
    @s,
    @p,
    @d;

And besides, it’s good form. You never know when something unicodey may sneak in to your string, and you don’t want to end up with question marks.

Concatenation Street

---

One big problem, and one that can happen suddenly, out of nowhere, without any rhyme or reason, and only on one server, and when you test it on another server it won’t happen, is string truncation because of implicit conversion.

See, when you put strings together, even if you declare the base parameter or variable to hold the string as a max data type, shorter strings will somehow cause SQL Server to decide that the result will be… something else.

Even more annoying is that even if you put an outer CONVERT(nvarchar(max), everything) around the whole ordeal, one string concatenation inside will still mess the whole works.

If you look at the dynamic SQL that I write, you’ll notice that certain shorter strings are surrounded with converts to a max type.

This is why you’ll see code that looks like this:

+
CONVERT
(
    nvarchar(MAX),
    CASE @new
         WHEN 1
         THEN
N'
qsp.plan_forcing_type_desc,
qsp.force_failure_count,
qsp.last_force_failure_reason_desc,
w.top_waits,'
         ELSE
N''
    END
)
+

This is also why I go heavy on having debugging to print string out: Because you can usually tell by which part of the string gets cut off where the implicit conversion happened.

Formation

---

Formatting dynamic SQL is especially important. Remember when I blogged about formatting T-SQL?

That goes doubly-double for dynamic SQL. I’ll often write and format the query first, and then make it dynamic so that SQL Prompt can work it’s 80% magic.

That dynamic SQL I showed you up there? I’d never put that out into the world for people to deal with. Rude.

It would look way more like this:

DECLARE
    @s nvarchar(MAX) = N'
SELECT 
/*I came from Erik Darling''s awesome stored procedure*/
    d.* 
FROM sys.databases AS d 
WHERE d.database_id = @d;
',
    @p nvarchar(MAX) = N'@d integer',
    @d integer = 1;

RAISERROR(@s, 0, 1) WITH NOWAIT;

EXEC sys.sp_executesql
    @s,
    @p,
    @d;

1. It’s formatted nicely
2. It’s aligned so that it pretty prints in RAISERROR
3. There are leading and trailing new lines so it separates nicely from other returned messaged
4. There’s a comment to tell me where it came from

Now if only there were a real dynamic SQL type so I didn’t have to use two apostrophes in there.

Thanks for reading!

Going Further

---

If this is the kind of SQL Server stuff you love learning about, you’ll love my training. I’m offering a 75% discount to my blog readers if you click from here. I’m also available for consulting if you just don’t have time for that, and need to solve database performance problems quickly. You can also get a quick, low cost health check with no phone time required.