Stored Procedures are complicated beasts in Microsoft SQL Server. There are many quirks you should be aware of when creating and calling them.
In this very brief tutorial, we’ll talk about one of those wacky rules.
Take a look at the following Customers and ContactInfo tables:
We have a stored procedure called addNewCustomer that inserts data for a new customer. The work is a bit complicated because it inserts a row into the Customers table first, then inserts a corresponding row into the ContactInfo table:
Take a look at the beginner-friendly tutorial on stored procedures if you aren’t sure what’s going on here.
Here’s a good example of calling the stored procedure:
Notice I outlined all the parameter names and values. This is the best practice when calling your stored procedures. You could omit the parameter names and just outline the values, like this:
But again, this is bad practice.
But there is yet another way to call a function with parameters
You can also outline some parameters with the parameter name and others without the parameter name. Here’s an example:
Notice I did not outline the parameter names for the @firstName, @lastName and @phoneNumber parameters, but I did outline the parameter names for the @emailAddr and @mailAddr parameters.
This is kinda weird though. I don’t recommend doing this, especially since there is a rule you need to be aware of:
As soon as you outline a parameter name in a call to a stored procedure, all the parameters after it must also outline their parameter name.
For example, this would not work:
Since we used the parameter name for the first parameter, all parameters after it must also outline the parameter name.
I cut off that error message, so here is the whole thing:
Must pass parameter number 2 and subsequent parameters as ‘@name = value’. After the form ‘@name = value’ has been used, all subsequent parameters must be passed in the form ‘@name = value’.
So do yourself a favor:
Just freaking outline all the parameter names every time you call a stored procedure.
It’s a best practice, and you’ll be less likely to confuse yourself and others!
Next Steps:
Leave a comment if you found this tutorial helpful!
If you found this tutorial helpful, you should consider downloading the following 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. Everything discussed in this tutorial can be found in the Ebook. 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!
Are you making these 3 other mistakes in your stored procedures?
Check out the tutorial to find out.
If you missed the full beginner-friendly introduction to stored procedures, check it out here:
SQL Server Stored Procedure: The Ultimate 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!
