All About SQL Server Stored Procedures: Correct Dynamic SQL Usage

All About SQL Server Stored Procedures: Correct Dynamic SQL Usage

---

Video Summary

In this video, I delve into the correct usage and safe implementation of dynamic SQL in SQL Server, covering everything from parameterization to object name protection. I discuss why `sp_executesql` is essential for safely executing dynamic SQL, highlighting its importance over `EXEC` when dealing with linked servers. Additionally, I explain how using `quote_name` can significantly enhance the security and reliability of your dynamic SQL by protecting against SQL injection via object names. The video also explores practical examples and best practices, such as handling Unicode characters, managing string lengths with `quote_name`, and ensuring proper concatenation to avoid truncations. By the end, you’ll have a clearer understanding of how to write safer, more efficient dynamic SQL that can withstand unexpected challenges.

Full Transcript

Erik Darling here with Darling Data, and we’ve got a wonderful video for you today. And this is going to be all about the correct usage of Dynamic SQL. Slides messed up, zoom is messed up, you click once, and da-da-da. Anyway, so this isn’t going to be the performance part of it. This is going to be how you write it correctly, safely, and some neat tricks you can do with it, and some gotchas around Dynamic SQL that you need to keep in mind when you’re writing it. So the performance one’s going to be next, I guess, because I don’t think I have a choice. I think that is just the forward arrow of time dictating to us when things will happen. But, man, there’s like a five second delay between when I say slide and this thing. slides. If you like this content, if you’re enjoying, I don’t know, maybe this series on store procedures, or maybe if you just like things around here generally, you have all sorts of ways available to you to keep this show going. You can sign up for a membership for as few as $4 a month. And you can say, good job, Erik. I appreciate you. You can like, you can comment, you can subscribe. Other ways to get some numbers increasing for the channel, always good to see upward trajectory with things. And if you want to ask me a question about SQL Server performance, or I don’t know, if anything else strikes your fancy, I guess you can ask. I can’t promise I’ll have a good answer, though. At this point, the entirety of my brain is wrapped around SQL Server performance stuff. So things outside of that, unless it’s about like, something in the gym.

I don’t know if I would have a terribly good answer for you. I could probably answer some questions about like, wine, scotch. Maybe some food stuff. But yeah, I don’t know. I’m pretty, I’m pretty dumb otherwise. That’s why they call me an E-core. Not a P-core. If you need help with your SQL Server. If you have reached the limits of your patience with some SQL Server performance issue, and you would like me to help you out, you can do that. And I can do all of this stuff. And more. And as always, my rates are reasonable. If you would like to get some training from me, wow, boy, wouldn’t that be nice. You can get all of mine for about 150 USD with that discount code.

Fully assembled down yonder in the video description as well. A SQL Saturday, New York City, May the 10th, 2025 with a performance pre-con by Andreas Walter on May the 9th. So that’s Friday. Saturday is the 10th. I’ll be there, of course. As an organizer, I won’t be speaking there, but I will be taking care of all sorts of things, wandering around the halls.

If I get a break, maybe I’ll sit at a table and just answer questions. I don’t know. Just don’t talk to me while I’m eating. I might bite you. Anyway, let’s talk about safe dynamic SQL use. Now, I spent a lot of time in my… Actually, let’s make sure I ran these things first. I don’t want to get caught flat-footed late and be like, oh, look at the dumb thing I did.

It happens enough in my life. I spent a lot of time talking about dynamic SQL and different reasons you would want to use it. In this video, we’re going to talk about some of the finer points of using it that make using it safer, easier, better, all that other stuff. I wish Microsoft would give us some sort of dynamic SQL data type or template thing where it would not require as much fuss to get dynamic SQL correct.

It would be just a far easier, far safer, far more approachable method of dynamically generating a string for execution. But, you know, instead we get fabric and ledger tables and big data clusters and such junk, such absolute junk, terrible products that who cares? All right, let’s not waste any more time on them. Anyway, first things first.

When you’re executing dynamic SQL, unless you need to do exec at a linked server, which I’m not going to be talking about here because, God, linked servers, you know… It’s funny to see how many questions people ask about linked servers because you’re just like… Would you give it up already? Would you just stop with the linked servers?

So unless you need to do exec at a linked server, you want to be using sp-execute-sql because that’s the only way that you can parameterize dynamic SQL safely. There are… In the performance section, we’ll talk about this stuff, but there are a few limited use cases where somewhat unsafe dynamic SQL would be the name of the game. In some cases, for use with very specific things that disagree with parameters, like filtered indexes.

But we’ll talk about that later. When you’re using sp-execute-sql, all of the arguments that you pass to… Well, two of the arguments that you pass to sp-execute-sql do need to be nvarkar.

So in this case, I have one incorrect, that one, and one correct, that one. And if I try to run this, we will get an error. And SQL Server will say, This procedure expects parameter statement of type n-text, n-char, n-var, n-car, n-var, n-char, n-var-char.

n-car, care, whatever. They’re not characters. They’re characters.

n-var-car, not char. They’re not characters either. It’s like vegetarians, right? If they were like vegans, they’d be vegetarians.

But they’re not. If we try to do this the wrong way here, we will get a different error, right? So here we have this one correct with n-var, n-var-car.

And this one we have incorrect with varkar. And if we try to run this one, of course, SQL Server will throw a different error and say, The procedure expects parameter params of type n-text, n-car, n-var-car.

So of course, neither of these work. But these are the rigorous demands of a very strongly typed language like SQL. I’m kidding.

So we get errors from that. But if we do everything correct and we have our strings set up correctly with these, then we will execute flawlessly. And this is how you want to do it when you’re writing it.

Besides, you never know when something Unicode-y or something outside of the standard character set might sneak in to one of the strings that you’re building. And when it does, I’ve seen all sorts of cases where database names, table names, something else ended up with a Unicode character in them.

And you will get just question marks. You will get one or more question marks depending on how many bytes your special character consumes. All right.

So that’s not good. You don’t want question marks showing up in there when you should have a valid character in there. The other thing is that when you are dealing with dynamic SQL, you always want to use quote name to protect your object names.

We’re going to talk about limitations of it, but quote name protects you from SQL injection via object name when just square brackets won’t. Now, square brackets do do something, right?

But they don’t do the full thing. Because quote name will actually double bracket stuff when it should, where just using square brackets won’t. So if I get rid of this table and then I run these two sections of code, the first one is…

Oh, boy. I did that all wonky, didn’t I? Look at that. Oh, Eric. Oh, you’re slipping. Slipping and tripping. Plus signs go at the end of the line, not at the beginning of the line. That’s just as bad as the leading comma.

So what I’m going to do is run this where I’m using the square brackets to enclose the string here. And then in the second one, I’m going to use quote name to enclose the string. And I think what’s interesting for both of these is that neither one of them actually does something, right?

Like they both throw an error, right? To not find store procedure print one. They both throw that error.

But in the case of the first one, the table actually does get dropped, right? Because when we try to… What do you call it?

When we try to select from it, we get invalid object name T down here, where that’s something we don’t get down here. So up in the results, we can see that we get an ID 2. And that’s from this second one, right?

That’s from… Jeez, I did not hit format on the script before I ran it. From the second batch where we inserted the value of 2 into it, right? We inserted a 1 up here.

For the first one, we didn’t get anything back. We just got an error saying invalid object name. And then we did this next one. And in this one, like quote name protected us from dropping the table in the dynamic SQL. So like quote name really does make your dynamic SQL safer all around.

So if you’re planning on accepting some combination, one kind of note up here. Sysname is the best data type to use generally for objects in SQL Server. That’ll keep you from messing a lot of stuff up.

But if you are planning on accepting fully qualified things like schema.object, database.schema.object, or database.schema.object, you do need slightly longer in VARCHAR strings because quote name has a limit. And we’ll talk about that in a minute.

Anyway, one reason why you need the bracket things in general are when SQL Server hits things that it can’t identify easily. So if I try to run this and create this clown table, SQL Server will say incorrect syntax near that. All right.

But if I say create table clown in brackets, I can do that. Where this gets interesting for dynamic SQL is that even if we use all the correct data types and we do everything the way we ought to, if we end up with this as a string, SQL Server will not be able to use that.

And we will get the same sort of error here with the incorrect syntax near this thing. Right. And that’s not a good time.

If we contrast that with this, where we use quote name on the table name, all of a sudden SQL Server will be able to figure that out just fine and select stuff from it. Right.

So using quote name there gives us that. In this case, you know, you could square bracket. You could just square bracket that, but our goal is to write the safest possible dynamic SQL, not to write just whatever the laziest, clumsiest dynamic SQL we can rethink of on the spot is. Now, one thing that is worth noting, though, is like, and this happens to me a lot in my store procedures, where I’m like, okay, I’ve got this identifier, right?

I’ve got a database server database schema table, whatever. And I stuck it in quote name. But now I want to go figure out if that object actually exists or not before I go try to do anything with it.

Is like none of the system views have object names quoted in them. Right. So like if you do something like this and you say, well, you know, I’m going to be safe and set my parameter or variable to the quoted version of something.

If you need to go look that thing up, you need to either like unquote it or like do like replace or just use parse name. Parse name is a handy built in function that is meant to do that. So like running these two things, what we get is for the one where we didn’t unquote stuff with parse name, we get nothing back.

For the one where we did unquote stuff with parse name, we get a valid result back. So if you’re going to like look at objects in here, you’re going to have, you’re going to want to do that anyway. Quote name, though, is, or we’ll talk about that in a second.

But you can, of course, use, you can, of course, like tell quote name anything to put in for what to use as the identifier. Right. You can like by default, it’s square brackets.

You can use quotes. You can use parentheses. You can use anything you want here as long as it’s like valid, I guess. But this is really only useful for dynamic SQL. Now, like if you have to embed one of these in a string and like you want to say like where like, you know, some object name equals something, you might want to use quote name to make, make your life easier with like the, like the single ticks and stuff.

Right. Because like if you just use that in straight, like, like a normal query, that’ll put, this will put double ticks around this. And there’s no object name called double tick, called tick clown tick.

Right. It’s just, it’s not there. Quote name does have a limitation, though. And that is 258 bytes. And keep in mind that the string, like the lengths that you see here are not characters, they are bytes.

So if we do this, we will get two strings back yelling, ah, at us. This one, you can see, does have the quote on it. I forget if this will get us to the very end.

It does. So we actually get both ends of the quote name. But if we say 129 here, then this will actually just return a null. Right.

So you can’t use quote name on very long strings. You can’t use quote name on anything longer than a single in VARCAR 128. You’ll notice that I have one, I have this defined as 129. It’s all about what you put in the string, though, right?

So if this were like in VARCAR max or something, like it would, it would return null if it were longer than 128 bytes and wouldn’t quote it. So quote name is kind of only useful for single parts of object identifiers. So like server name, database name, schema name, object name, whatever that object is, whether it’s a procedure, table, view, function, whatever.

So just, you know, be careful with quote name because you can have some unexpected disappearing strings in there. Another problem that I run into a lot with SQL Server that is difficult to reproduce reliably is when you’re concatenating dynamic strings together, a lot of the time what you’ll have, like what might happen is, and this is like seemingly completely random. Like there’s just some weird like implicit conversion that happens and your beautiful dynamic string gets truncated.

And this happens a lot when you’re just like putting some smaller portion of a, like tacking some smaller portion of a string onto your longer string. So what I end up having to do in a lot of my procedures is whenever I need to like, like add on something in here, I need to explicitly convert whatever I’m adding on to be another in VARCAR max. So I don’t like, I don’t get the string doesn’t concatenate surprise.

And I’m sitting there like, like trying, like printing it out and like, wait a minute, there’s part of the string missing. What, where did it go? Cause like, like I know print has limits on it and you know, you can run into, you can run into problems there. So, but if you’re like printing sub strings and you’re like, wait a minute, I’m printing this sub string and my string is still disappearing.

You most likely have to convert some like tacked on addition to the string with, with convert. I know that the concat function exists. And some of my procedures do run in versions of SQL Server where the concat function is available.

Like they’re only running that like quickie store. I just don’t use it because, you know, I distribute my scripts as like, as a whole, pretty much like you can get that. We can get like the install all file.

And I don’t want someone on like an older version of SQL Server that doesn’t, maybe doesn’t have concat where some of the store procedures are still valid and will still return results and give you information back. I don’t want those to like error out because of something in a different file. So another thing that’s important with dynamic SQL is formatting.

Most of the time I will, I will write as much of the query out as I can and format it and then paste that into my dynamic SQL string and do whatever other stuff I need to. So all of my dynamic SQL is formatted in exactly the way that I would format a normal query. And I am pretty happy with that because then I have a nice legible dynamic string that I can copy and paste out of here and make my life is a lot easier.

You know, side note on style stuff, whatever you’re writing dynamic SQL, when you can put a comment in the string that tells people what store procedure it comes from. Output is another very, very useful thing that you can do or rather output parameters or output values is a very useful thing that you can do with dynamic SQL. And in this case, you can actually use these things as input and output values.

So this is kind of a neat trick where I’m going to set I equal to zero. I’m going to set E equal to the max database ID. I got my string here, right?

So I got all this stuff lined up and then I’m going to pass. I have the same parameter in here. I and I. So what I’m doing is selecting the top one database ID where the database ID is greater than I. And then down here in a loop, I’m just going to say while I is less than E, well, like I is less than the max database ID, just keep outputting that and running stuff.

And what we can do there is actually just pass that in and out where we’re looking at database one, two, three, four, five, six, seven, eight, nine of nine. So you can use output to drive loops with dynamic SQL, which is pretty neat because then you don’t have to either keep rerunning syntax to find some value. And you don’t have to, in your loop, you don’t have to increment.

You don’t have to remember to increment anything here. It’s almost as cool as a cursor where you don’t have to remember to be like, oh, set I plus equals one or something to go find the next value. It’s especially helpful when you might not have contiguous values.

So like, let’s say I had database IDs one, three, five, seven, nine. I wouldn’t waste time looking for database IDs two, four, six, eight. So you can do neat stuff with output there.

But what I use output for a lot in the context of dynamic SQL, and here’s actually a good example of using quote name with the single ticks to make string quoting within the dynamic string easier. What I end up doing for it a lot is using it to validate the existence of other things and other databases. So what I’m doing here is I’m using the output stuff to figure out if the schema DBO exists in the master database.

And so if I run this, SQL Server will be like, does the output schema exist? Go run this query in the master database context where name equals, oops, where name equals DBO. And then it’ll say, hey, it looks like that schema does exist.

All right. But if I change this to like something stupid, like typing, if I change this to something stupid that obviously doesn’t exist and I go look for it, SQL Server will be, oh, I forgot to highlight the rest of that string.

That’s the important part, isn’t it? If I highlight this, we’ll get an error that says it looks like the schema barf doesn’t exist in that database. Now, like granted, like schema checking is one of those things where you could probably skip over checking to see if DBO exists. Right.

Like, like obviously it’s going to be there. Whether you have permission and access to it might be a different matter, but it’s there. Now, the last thing I want to talk about in this video is when you are accepting any object name, like even using quote name, you know, sometimes you want to be extra safe. Right.

And the way to be extra safe is to never concatenate or even using quote name, put those into whatever dynamic string you’re going to execute. So what I’ll do sometimes if I need to be extra, extra safe is I’ll accept the parameters for whatever in here and then I’ll declare safe local variables for those things here. Right.

And what I’ll do is I will only use the parameter values to look up safe values. Right. So like I’ll go to sys.databases and based on the database ID, like if this is if there’s if this is invalid, like you can’t execute anything in here. It’s not dynamic.

See, well, this isn’t going to execute like some malicious string, but this will just set the safe database name to the name that aligns with the database ID. So we ditto this in here with like the safe schema and table. We just don’t join sys.tables assist.schemas.

And sometimes it’s useful to put like a backup thing in here just in case like but, you know, you don’t obviously don’t have to do that. And then for columns, what I’ll do and I know some of these functions aren’t available in all the every version of SQL Server and every compat level. But this is just sort of a brevity here.

I’m going to use string ag and string split to do this. And what and what this will get you is like a safe list of columns based on whatever someone supplied in the column column names parameter. Right.

So the safe columns get assigned to a comma separated list there. And each one of those each one of those column names gets put gets put in quote name. Right. So that that’s all worked out.

Now, you want to like do some checking, be like, hey, if any of these come back. It’s null. Just say, you know, something was invalid and whatever. Now, one thing with the safe columns and I’ll show you this when we execute stuff.

But it’s kind of neat about the safe columns thing is if someone gets one column wrong, the store procedure won’t error out. It’ll just skip that column. So then, of course, this will be our dynamic SQL where we’re still using quote name on all this stuff just in case there’s anything weird in them.

But like I know we could have said that. We could have said we could have set some of this stuff earlier. We could have said like quote name, whatever, but it doesn’t matter so much.

Then we’ll set that stuff in there. But let’s just make sure this is created and everything is good with this. And then let’s walk through a couple executions of this.

So this with like doing this will return results. Right. This will be fine. Doing this, we get all the results back. If we put in invalid database name and we just put a Z on there, this will say, hey, invalid database.

And if we do the same thing to the schema name, it’ll say invalid table or schema. And if we can see the debas there, right, that’s the incorrect one. And then if we do that for album, let’s say that make that albums.

This will throw the invalid table, like invalid schema or table for albums. But here’s what I was talking about with the column list. And let’s say we just put a Z at the end here.

Now, actually, I’m going to run this once. Let’s make another copy of this. And let me just show you how this is different. If you run these two, notice the track ID column isn’t this one, but the track ID column just gets emitted from this one.

And that’s because when we look for stuff up here, like that track ID just doesn’t make the in clause for this. If we were to change this drastically and we were to do something like maybe only select one column and have it have a Z at the end, then we would get the column list thing here.

Now, if you were like really interested in making this extra like verbose and whatever, you could, of course, like, you know, compare the list of columns passed in to the list of safe columns or the list of columns that you find and be like, hey, this column was invalid.

But maybe look at that. I just didn’t do that here. Anyway, this is about as much about like safe and sound and good dynamic SQL uses I can fit into a video a reasonable length. It’s a lot to remember and a lot to think about.

But hopefully the more you do it, the easier and more intuitive that becomes. So anyway, I hope you enjoyed yourselves. I hope you learned something.

And I will see you in the next video where we will talk about dynamic SQL, like I said before, in the context of performance, which is generally what we care about. But at some point, we also care about tables not getting dropped and data not getting exfiltrated or vandalized and all that good stuff.

So anyway, we’ll do that. And I forget what’s after dynamic SQL, but that’s OK. It’s OK.

Once you know dynamic SQL, what more do you need, really? Anyway, thank you for watching.

Going Further

---

If this is the kind of SQL Server stuff you love learning about, you’ll love my training. Blog readers get 25% off the Everything Bundle — over 100 hours of performance tuning content. Need hands-on help? I offer consulting engagements from targeted investigations to ongoing retainers. Want a quick sanity check before committing to a full engagement? Schedule a call — no commitment required.