All About SQL Server Stored Procedures: Formatting
Video Summary
In this video, I dive into the world of T-SQL code formatting, sharing my strong opinions on best practices that can significantly improve readability and maintainability. I start by addressing some common issues like tab usage (which I strongly dislike), leading commas, and inconsistent capitalization—both all uppercase and all lowercase. These habits, while seemingly minor, can lead to confusion when trying to understand complex queries or stored procedures. I also emphasize the importance of proper indentation, new lines for logical sections, and grouping related code together to make debugging and maintenance easier. By organizing code in a structured manner, you not only improve your own understanding but also make it much simpler for others who might need to work with your code in the future.
Full Transcript
Erik Darling here with Darling Data. And I’ve got a big smile on my face today because me and Bats are going to be talking about one of our favorite subjects, and that is code formatting. Now, code formatting for me of the T-SQL variety essentially comes in, well, I mean, I guess you could call it three measures. One is the way that you write the code. It’s a query, right? The way that you indent things, capitalize things, so forth. Place your commas, right? And also the way that you organize code, so larger groups of code, which you might associate with something like a stored procedure. You are doing all of the things that sort of belong together. all together. So when you declare variables, you’re doing that all in one place. If you’re setting those variables equal to something else, you’re also doing that as much as possible all in one place. We’ll talk about an exception for that. You’re creating temp tables, you’re doing that all in one place and so forth because one of the worst things that I run into when dealing with client code is stuff that’s been added on over the years where, you know, you’re reading through a stored procedure and you’re like, oh, I’ve accounted for all these things. And then you get 2000 lines in and you realize some other knucklehead has, you know, added all these other things and declared stuff and created tables like down through the second half of the procedure.
And now it’s just like this mishmash, right? It’s just like it makes things a lot harder to follow. And when you were trying to figure out like the things that are in play for like what you have to work with, it is much easier and much more convenient to have everything as much as possible together. So that’s what we’re going to be talking about in the formatting episode of our journey through SQL Server Store procedures. But before we do all that, of course, we have to talk about the normal sort of stuff and how you can engage with your dear friend, Erik Darling, more. If you would like to support this channel, helpful little link right in the video description. You click on it, you give me four bucks a month, we’re all happy. You can choose more than four bucks a month if you’d like. But one pre-tax cup of one pre-tax single shot of New York espresso is just about as low as you can go there.
If you are for some reason opposed to caffeinated beverages, you can like, you can comment, you can subscribe. And if you would like to ask me a question that shows up in an Office Hours episode, there’s a link also in the video description that will take you to this thing and you can submit your questions. If you are beyond the help of YouTube lessons and like basically anything else and you need help performance tuning your SQL Server, I am available to do all of these things. And as always, my rates are reasonable. Also reasonable are my rates for SQL Server training. I’ve got about 24 plus hours of it. You can get it for about 150 USD. That belongs to you for the rest of your life. No backseas. Don’t want it back. You’ve probably spit on it.
As far as upcoming events go, SQL Saturday, New York City 2025 is taking place May the 10th. If you should attend, if you are a company watching this, you should sponsor. And we have a pre-con on May the 9th, all about performance tuning with Andreas Volter. So, he’s a smart fella who used to work at Microsoft. Probably the smartest thing he’s ever done is leave Microsoft. But you know, we’re all happy to have him back in the real world. So, with that out of the way, let’s talk about code formatting here. Now, that was the wrong tab to have up front. So, first, there are a list of things that I have rather strong feelings about.
I strongly dislike tabs strongly dislike tabs I realize that there every time I talk about my dislike for tabs there is a chorus of people talking about the the visually impaired and how when they use tabs they can do things better and and if you are in that situation if you’re in that condition and you and you want to use tabs I don’t blame you I used to work with a fellow who did QA he was in real tough shape and in the eyeball department you would walk by his desk and there would be like a single letter on the screen like I don’t I don’t know how he got anything done but he was he was very good in the QA in the QA department apparently he had he had worked out whatever whatever he had to get whatever he had to work out in order to to to be very proficient in his job but for me when I’m writing t-sql and let’s let’s be honest most of the time I am maintaining this t-sql on my own there are there are a handful of contributors who I am ever grateful for but I find tabs or more specifically when tabs and spaces get mixed to be quite offensive there’s nothing worse than when you’re trying to like format and line up your code and you’re like you know you hit enter and then you like delete two spaces and then you go to delete that third space and it jumps back like a fight two full tab stops for some reason you’re like a lot of stuff going on in there other things that I find offensive are leading commas anyone who says it makes taking things in and out of the select list easier has naked never taken the first thing out of this select list so that that causes problems for the 40 other columns you’re selecting things that I dislike but perhaps feel somewhat less strongly about all uppercase and all lowercase all lowercase of course just looks strangely juvenile and all uppercase looks like Oracle shouting I really dislike when new things are not on new lines so things like from join on where and or order by and group by I guess I guess partition by should be in that list too I think all of these things are deserving of their own lines so that you can figure out exactly where things start and end it becomes very confusing when you know just like it was like you’re reading a windowing function or something and it’s just like row number over partition by order like all across on one line these things deserve your attention select queries where it’s like select long select list from table and then like down here it’s like where how did you get that like put stuff where everyone can see it it’s especially helpful because you know you are generally reading t SQL in a very like sort of top-down fashion and being like often like the list of columns in the select list is maybe not quite as important as figuring out like oh we’re selecting from these tables join to these tables you know things like that so this stuff really does deserve new lines a lack of indenting and t SQL makes things incredibly confusing and proper indenting really does lead to it being a lot easier for you to figure out which blocks of things belong together especially if you are doing the in and end blocks if you have parentheses contents it makes it very much much easier to figure things out when things are indented and you can see this block belongs here belongs to this thing leaving as out of table aliases it’s just somewhat irksome there was actually kind of a funny story here is there’s one client who I was working with and we were looking at their code and we realized that they like they were using the old style like from table no lock hint right not with no lock but no lock was spelled wrong and they were like wait how does that even work and I was like because SQL Server is not interpreting no lock with two K’s is a hint it’s it’s it’s interpreting it as a table alias so that was kind of funny I strongly prefer the column equals expression aliasing because it lines up all the column names where you can read through them and see where things are when it’s like like you know you write out this whole complex long expression and then it’s as something it like the context switch to look all the way over here to see a column name and all the other column names are here not so not so great top expressions without parentheses should be banned I hate those uh align parentheses tend to work out much better because it’s a lot easier to mentally grasp what belongs in this group of things uh when you do stuff like this and give things weird little hugs it just looks strange it doesn’t look good uh there are other things that I I will usually uh tweak and tinker with as I’m formatting code but uh that’s about you know that’s about the list of like the the the most important stuff for me I think um when you think about formatting code though like I said earlier it’s not just uh merely about where like you know this stuff up there like the indenting and the grouping and the stuff like that it makes like when you’re writing code in a procedure it’s really really helpful to have everything that belongs that like like like like has a certain action like declaring variables setting variables creating temp tables stuff like that it makes a lot more sense to do all that stuff all together so you know which section of code is responsible for that so if there’s ever a problem you know where to go like if you’re you know declaring variables like we’re going to like 2 000 lines into a store procedure or creating a table like 3 000 lines into a store procedure and there’s ever a problem with that it’s just like okay now well it’s not up with the rest of them now I gotta go down way down here to go do stuff it’s annoying not a good way to spend your time um so the things that I will lend some leniency to uh in these regards it are select into and the reason why I will uh I will allow some leniency here is because there are certain times when um you you you you want to just do a simple select into it makes life easier um perhaps there is some other benefit to it um maybe for some reason creating the table and doing the insert you are just not getting a parallel fully parallel insert from it even with a tab lock hint no sql server gets weird sometimes uh so sometimes a select into is necessary and if that’s way down in a store procedure I get it it’s not not completely off limits the other thing that um i am lenient with is uh declaring cursors and I’m going to show you an example where I was even lenient with myself when it came to declaring a cursor of course uh it is perhaps extra strange for me because I I strongly prefer cursor variables over regular named cursors because the cool thing about cursor variables is you don’t need to close or deallocate them technically you only need to deallocate because deallocate closes too but when you use cursor variables you don’t have to do what you don’t have to remember to do either one sql server scopes them and like like a normal variable and closes them out when you’re when you’re done with the the block of code so let’s take a look at my store procedure sp log hunter and I’m using this one because um I think it is the shortest of my procedures and um that that’s good for all of us because I’ve already been talking for like almost 13 minutes and we haven’t looked at any code yet and I don’t want to spend a half hour going through sp human events or quickie store where there is an abundance of code uh though I would say that if you ever want good examples of formatting dynamic sql there there are there are very good examples in those procedures so here’s sp log hunter um we talked about anti settings uh before um like I said a lot of this stuff uh like I put at the top because this is stuff people should like be aware of like care about when they’re you know doing things with store procedures since I’m never going to be calling this from an application where it might have different ANSI settings nor am I overly concerned about filtered indexes computed columns or index views it doesn’t matter for me to put this up here uh then you know some you know a little bit of green text a little bit of information about what’s going on in here right uh where to go to get support even though everyone just sends me emails anyway uh and then of course we have our parameters and I like to document the parameters up here where they actually live in case you’re just looking at the script file but I also like to do that in a little help section so sql server has a couple very helpful views um sys.parameters sys.object all objects and well you could do sys.objects and sys.types and what you can do is you can actually get the list of parameters for a specific store procedure and you can like also like put them in a help section here where you know you can just say hey this is what this means you can put like all sorts of good information in there so I like to include a description valid inputs and default values for them so everyone knows where they’re going uh MIT license stuff because I’m cool like that and then uh moving into like grouping stuff where it belongs uh before I get into most anything else uh what I want to do is make sure that I am conditionally able to execute uh the procedure that I’m running so stuff like checking to make sure that we’re SA right that’s a good way to figure out if I’m going to be able to run this thing uh if we’re an RDS I can’t use I can’t read the error log the same way as I can when I’m on regular SQL Server even managed instance does allow you to get the error logs I think I’m pretty sure it does uh but um Azure SQL DB does not um RDS does allow you to get to the error log but it’s through this other procedure that doesn’t have the same parameters and searching stuff in there I am thinking about working out a fix with that where I would just read in the whole error log and then do the search off that but I don’t know we’ll see um but here’s our check for Azure SQL DB uh where we will we will not attempt to run this here if that’s going on there’s one other check that uh results in a return right that results in a full error and that’s if someone is searching for a language id that does not exist right so if you search for a language id 99 99 99 or something uh that’s not going to happen for you but then there’s uh fixes to stuff right so this is where if parameters are set to odd values I’ll attempt to psychically correct them so that the store procedure runs without any weird mixed up stuff going on uh so there’s a bunch of that and then down a little bit further this is where I declare the variables that I need to do different things in the procedure but this all happens in one place uh and since um I have uh what do you call it uh not a whole lot going on in this one there’s a very limited list of uh variables in here and I can set those very easily here where I care about them uh down here this is where I create my temp tables and this all happens in the same place right all the temp tables get created and then we uh go and we um do some work right and this is where we actually start doing things in the procedure to you know look at what we have available so uh I talked about this in another video this is all on one line you know just because it’s a it’s a debug statement maybe not the most consistent thing but uh sometimes it just is a little bit more convenient for your sake to just sort of stick stuff in like a debug thing in here like this so that you know your store procedure isn’t 10 000 lines of perfectly formatted debugs like this isn’t too bad but you know stuff that I like to do is make sure that when I am debugging things with the temp table I include the table name in what I’m selecting from so I know what results I’m looking at because if I have a like you saw I had like six or eight temp tables up there what I don’t want is a bunch of selecting from temp tables and not knowing where the results are coming from which like especially if one is empty like what do you do right like it’s going to be hard to figure out what you’re dealing with uh I don’t know some pretty boring stuff in here uh there was something else that I wanted to get down to I believe it was the cursor variable and that is right here so this is another place where I’m sort of lenient about when things do get declared and set up uh where I declare the cursor variable here and I set the options for that here it just makes like it it just makes a little bit more sense with stuff like this to have have it declared where it’s going to be used there aren’t a lot of other cases where I feel the same way about it about this particular thing but for me uh declaring cursors is one of those things because there’s not a lot of sense in declaring a cursor and then having like way up at the top and then having someone be like well wait a minute when when does this cursor happen right so like for me declaring the cursor inside of the like or like right around the looping code is the smartest thing to do right that makes the most sense to me uh and then uh you know helpful debug stuff so this is a multi-line debug I do have the begin and end and everything on its own line and then down here there’s just a little bit more of the uh what do you call it uh more debug stuff there was something else I was supposed to be looking for in here uh well yeah when we return the the results right uh so that’s that’s sort of an important stuff but anyway when uh we when we’re like returning the results again make sure that the table name is in there so I know that this is the result of like everything that got put into the error log table that of like the stuff messages we cared about right so like this whole big block of stuff way up here uh these are all the messages that I want to search for in the error log this is the important stuff where it’s like might be something wrong might be good informational stuff so down here is where I select out of that table and then if there if we hit any errors in the store procedure I log those to a temp table and if there’s anything in the error error like that like if any of the command statements error out in there those get caught here and I return those only if there’s anything in there so uh you know apart from the like actual query formatting stuff that I feel strongly about uh you know tabs commas new lines for things uppercase lowercase print parentheses indenting things like that uh I really it does make your life and the life of everyone who has to deal with your code a lot easier if you put everything that performs a specific function all together so again like declaring the variable setting the variables creating 10 tables table like table variables things like that uh it really does behoove you to do those things so that when other people go into work with your code they’re sort of aware of like just how much of these things like they’re gonna have to look at and work with and deal with and everything else uh it also just helps to be you know well organized uh when you’re when you’re writing uh larger complex pieces of code because you know there there’s there’s probably going to be enough complicated query logic without there being a whole lot of disorganized um code logic as well so uh anyway that’s how I feel about formatting uh I hope you enjoyed yourselves I hope you learned something uh I hope that you will take some of this stuff to heart and when you’re working with queries and stored procedures you’ll start to organize and format and uh you know get things looking a little bit nicer and tidier and uh contribute to nice clean readable code um you know when when folks out there in the world start talking about how CTE make things more readable they don’t they sure don’t nicely formatted code makes things a whole lot more readable and understandable than all of the CTE in the entire world so thank you for watching and I will see you in the next video and uh goodbye
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.