A Neat Trick with Using SELECT to Assign Variable Values
Video Summary
In this video, I delve into the nuances of variable assignment in SQL Server using `SET` and `SELECT`. Specifically, we explore how these commands behave differently when no rows are found, highlighting the peculiarities of `SET` where it overwrites variables with nulls, while `SELECT` maintains or overwrites them based on the result set. I demonstrate a clever workaround to make `SET` behave more like `SELECT`, especially useful in scenarios involving multivariable assignment within loops. This technique ensures that your code remains robust and avoids potential infinite loops or unexpected behavior when dealing with non-existent rows, providing a valuable lesson for anyone working with dynamic SQL assignments.
Full Transcript
Erik Darling here with Darling Data. And we have a fun little video today where we’re going to talk about how, like, if you, like, back in, like, the, I think if you watched, like, the, like, the earlier, like, Learn T-SQL, either, if you, if you were kind enough to purchase the course, well, thank you. But if you saw some of the, like, preview videos here on YouTube, one of, in one of them I talk about the difference between, some of the differences between set and select when assigning variable values and how the behavior is strange and how, like, set is kind of annoying because you can’t do, like, multiple variable assignments with it. But you can with select, but select and set act differently when a row is not found and how variable values are either maintained or overwritten with nulls.
So we’re going to talk about how to make select behave more like set in that video when you’re doing multivariable assignment. I mean, technically it would work with single variable assignment too, but you may not want to write this into every single query. Anyway, down in the old video description, boy, oh boy, if you, if you want to hire me for consulting, you, you can do it.
The power is in your hands. As long as the credit card is in your hands, the power is in your hands. You can buy my training.
You can become a supporting member of the channel. Likewise, you can do things for free. Free stuff.
Ask me office hours questions, though that may soon cost $5. $10. Maybe up to $25. Because some of the questions that come in, I think, I think, I think some, some form of angel dust was involved with that.
And if you enjoy this content, you can, of course, like, subscribe, and tell a friend or two or three or a thousand. However many you have, just break out the old Rolodex, flip through, shoot everyone a fax, say, hey, check out that Darling Data YouTube channel about SQL Server. Out in the world, yeehaw!
Nashville and Chicago in March. Back to back weekends, 6th and 7th, 13th and 14th. I will be doing pre-cons at both on Advanced T-SQL. And you should go.
You should come see me. You should go to the events. You should get out and support the wider data community before, you know, I don’t mean to sound morbid here, but any time you do something could be the last time you do it. So, get out into the world and do some, do some good and fun things, like come to data platform events.
Anyway, Merry Christmas. Let’s look at how to make set behave more like select. So, the first thing we have to do to set this adventure up is look at a couple selects from the users table.
You will notice that if I select, and I’m going to show off a fun SSMS 2022 thing here. I’m going to dynamically zoom in on these results. ID5 returns Mr. John Galloway.
I don’t know John personally. He seems wonderful. I appreciate when, I appreciate that he is in my first name boat where everyone probably looks at his name like in an email or something like that and still finds a way to spell it incorrectly when they type it. So, we have John Galloway here for ID5.
And then for ID6, we turn no rows, right? There is not a single result down here. There is nothing. I’m not hiding anything from you, I promise. But that is for ID6.
Now, if we were to do this, let’s say under normal circumstances, and we were to run this set of queries here, where we declare some variables, and we set them equal to stuff for ID5, and then we look at the contents, and then we set them equal to ID6, and we look at the contents. These are the results that we get back, right? Because no row is produced for ID6, nothing is overwritten, right?
We may just have the same variable values in here. Golly. Anyway.
Pardon me. We have this here. So, this can get people into a lot of trouble if they are unaware of this behavior and they are assigning variables to something in a loop. And then all of a sudden, they stop finding new values.
And so, they just keep either assigning nothing to this row and, like, reprocessing whatever row is in that current set of variables. Or, I don’t know, just, like, infinite loop, right? They’re just, like, they’ll, like, process this thing multiple times, or they’ll end up in an infinite loop processing the same thing over and over again, right?
Not a good time. Something to be very much aware of when writing this sort of thing, writing anything that does variable assignment. Now, set, of course, doesn’t do this, but you can’t do multivariable assignment with set because what happens is you quickly find out you can’t do it, right?
Unless you hit the, like, we would have to write three separate set queries to the user’s table in order to have that work. So, something that I picked up while working with a client was that you can have a dummy row, right? And you can do something like this with the values clause.
You could, of course, just say select null or something if you felt like it. You don’t have to use values, but I like to use values because I don’t have enough of them. Quite frankly, there’s a real shortage of values in the Darling household.
So, every opportunity I have to use values, gosh darn it, I try to get it in there. So, what you can do is something like this where you say select from values and then outer apply to your query that would do the variable assignment and then at some point do the variable assignment out here. You couldn’t do it in here.
That wouldn’t work. So, if we do this and actually I think we have a few things to run down here. Run all this stuff. Now, what we have is John Galloway set when we hit ID 5 and then we have overwritten those rows with null.
Or rather, we have overwritten those variables with null values when we did not find a row for ID 6. So, this is a nice way of making sure that your loop hits some null check condition or something when it doesn’t find anything to do. And you can do this without having to like figure out like, hey, what are the current, like what are the last set of values that I just processed?
Do I have that same set of values? Or like something like that. You know, I guess you could, you know, if you’re feeling fancy, you could do something with like row count and be like, well, did I find any rows there?
No? Okay. Well, this is just another way of doing that. This might actually be a little bit more flexible in a lot of ways because, you know, if you find nulls, you might have to go do something else or something.
Right? You don’t want to necessarily like break out of stuff. You just might want to say, hey, these are all null. Or, hey, one of these is null and it shouldn’t be.
Or, hey, two of these are null and they shouldn’t be. Or something like that. Right? There’s like all sorts of things that, you know, having this assigned would allow you to, you know, maybe explore within your loop and say, hey, that ain’t right. So, just a neat kind of trick that I learned about.
And I thought you would enjoy. That’s all I had for this one. Thank you for watching. I hope you enjoyed yourselves.
I hope you learned something. And I will see you in tomorrow’s video. I do forget what the topic is. You’ll have to forgive me. But it is one of these three windows over here. So, there is something more to talk about.
Isn’t that wonderful? All right. Thank you.
Going Further
If this is the kind of SQL Server stuff you love learning about, you’ll love my training. I’m offering a 25% 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.