Stored procedures are a major part of any SQL Server database. They are the tools we use to introduce programming logic into our databases, and allow us to repeatedly run complex code.
If you are new to SQL Server, you should definitely focus much of your learning towards understanding how to write stored procedures and how to use them properly. They are the one of the most common objects we will use as databases developers. You might also benefit from downloading the following FREE Ebook:
FREE Ebook on SQL Server Stored Procedures!
Everything discussed in this tutorial can be found in this FREE Ebook. It discusses all the key points you need to understand about stored procedures in Microsoft SQL Server. It will definitely be an excellent resource for you to keep and reference throughout your career as a data professional. Make sure you get it today!
In this very brief tutorial, we’ll go over some of the most common mistakes I see when working with stored procedures in Microsoft SQL Server and what you can do to avoid them.
Let’s jump right in:
Mistake # 1: Starting your stored procedure names with “SP”
When defining a stored procedure, you want to give it a meaningful name, as you should with any object in SQL Server.
When naming a stored procedure, it might be tempting to start it with the prefix “SP” for Stored Procedure. But when you do that, you are making the execution of that procedure slightly less efficient.
Microsoft SQL Server comes with many system stored procedures by default. They are kept in the Master database, as demonstrated here:
There are A TON of system stored procedures installed with SQL Server, many more than shown in the screenshot.
But notice they all start with “SP“.
When you define your user-defined procedures starting with the letters “SP“, and then execute those procedures, SQL Server will think you are trying to run a system procedure and will look for that procedure in the list of system stored procedures in the Master database.
Of course, it won’t be there. Upon not finding it, SQL Server will then look in your local database for the procedure (where we knew it was all along).
Does that sound like a huge waste of time? (say yes)
You can help SQL Server by not naming your user-defined procedures with the prefix “SP“. That way, it will look in your local database for the procedure right from the get-go.
Mistake # 2: Not outlining parameter names when you call your stored procedures
Take a look at the following DVDCollection table:
I created a very simple stored procedure to insert rows into this table:
Notice the order of the parameters: Title, Director, Duration.
Let’s insert a row for the movie Hot Fuzz
If I execute this procedure and mix up the order of the parameters, it’s fine as long as I outline the names of the parameters in my call, like this:
But now, let’s try to insert the same row, while still mixing the order of the parameters, but this time we WON’T specify the parameter names:
Folks, when you leave off the parameter names, you must outline your values in the order that the procedure defines them. This example is trying to write the number 121 to the Title column, the word ‘Wright’ to the Director column (which coincidentally is correct), and the word ‘Hot Fuzz’ to the DurationInMinutes column which is an INT data type. That last thing definitely won’t work. Ergo, the error message.
I like to be explicit when I call my procedures, so I will usually outline the parameter names in my calls. I do this even if those parameter names are listed in the correct order and therefore aren’t technically required.
But if you mix up your parameters (either on purpose or on accident), understand it’s fine as long as the parameter names are used in the call. Otherwise, you could insert invalid data or you could get an error message.
Mistake # 3: Not properly handling ROLLBACKS from within your stored procedures
This last one is a doozy.
Imagine you have a stored procedure that starts a transaction. We’ll modify our INSERT_NEW_DVD procedure as an example:
I’ll briefly describe what is happening in this procedure. First, we open a transaction using BEGIN TRAN. Then within that transaction, we perform our INSERT into the DVDCollection table. If there were no error messages returned by the SQL Server while performing that INSERT, we go ahead and COMMIT the changes. Otherwise, if there was a problem, we ROLLBACK the transaction and do not save the changes to the database.
Need a rundown on transactions? I got you:Â SQL Server Transactions: An Introduction for Beginners
There is a huge thing you need to understand about ROLLBACK. The ROLLBACK statement will roll back all transactions in a chain of transactions, and not just the current one. For example, what if we were already in a transaction when we called our stored procedure? If there was an error, and we did the ROLLBACK, it would undo our inner transaction (of course), but it would also undo that outer transaction, too.
Whoopsie daisies. We just ended a transaction that was created somewhere else.
There is a way to ensure that if you need to do a ROLLBACK in your stored procedure, you only roll back that transaction and not any outer transaction that may have existed when your procedure was called.
There is an entire tutorial dedicated to that discussion. The key is to get the count of currently open transactions as soon as you enter the stored procedure, and also to name your transaction.
Check out the full tutorial here:
Are you making this ROLLBACK mistake in your stored procedures?
Next steps:
Leave a comment if you found this tutorial helpful!
Do you agree with the list? What are some other best practices you think should be done when working with stored procedures? I want to know! Leave a comment down below.Â
Don’t forget to download your FREE Ebook:
FREE Ebook on SQL Server Stored Procedures!
This FREE Ebook discusses all the key points you need to understand about stored procedures in Microsoft SQL Server. It will definitely be an excellent resource for you to keep and reference throughout your career as a data professional. Make sure you get it today!
Be sure to check out my other in-depth tutorials on other widely used SQL Server database objects:
-
SQL Server User Defined Functions: A Complete Guide.
-
What is a View in SQL Server? : A Guide for Beginners.
Thank you very much for reading!
Make sure you subscribe to my newsletter to receive special offers and notifications anytime a new tutorial is released!
If you have any questions, leave a comment. Or better yet, send me an email!
