If you are planning on spending any time at all working with SQL Server, you should probably expect to encounter stored procedures very early in your career.
Stored procedures are very common objects found in SQL Server databases. They are basically what we use to write programming logic within SQL Server (even though SQL isn’t a programming language).
Are you just starting out with SQL Server, and aren’t too sure what a stored procedure is and how we use them? In this tutorial, we will discuss everything you need to know!
Here are the topics we will be covering in this tutorial:
- What is a Stored Procedure?
- An example of a Stored Procedure
- Calling a Stored Procedure
- What are the limitations of Stored Procedures?
- Tips and tricks
Everything in this tutorial can also be found in 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. 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!
Let’s get into it.
1. What is a Stored Procedure?
A stored procedure is a database object used to store complex SQL code that can be ran easily and repeatedly using simple commands. They can take parameters as input, as well as return values or a result set to the caller as output parameters.
There are many things you can do inside the definition of a stored procedure that you cannot do in a function, making them a better solution in some scenarios.
If that sounds like a different language to you, it’s ok. We’ll break it down by showing you some examples.
How can Stored Procedures help us?
Stored procedures can be used to store complex SQL code as a database object in your database. That way, you don’t need to remember that complex code every time you need that work performed.
Let’s take a look at an example of “complex code” to see how a stored procedure could help us.
2. An example of a Stored Procedure
To provide an example of a stored procedure, we should first set up some data to test with.
Let’s say we the following Customers and ContactInfo tables in our database:
Here are the CREATE and INSERT statements to create this data in your own environment if you want to follow along:
CREATE TABLE Customers(
CustID int IDENTITY(50,5) NOT NULL,
FirstName varchar(20) NULL,
LastName varchar(20) NULL
)
INSERT INTO Customers (FirstName, LastName)
VALUES
('Joshua', 'Porter'),
('Andrew', 'Bluefield'),
('Jack', 'Donovan'),
('Cindy', 'Thatcher'),
('Gordon', 'Acres'),
('Gretchen', 'Hamilton'),
('Brenda','Maxwell')
CREATE TABLE ContactInfo(
ContactID int NOT NULL IDENTITY(5000,1),
CustID int NOT NULL,
Phone varchar(13) NULL,
Email varchar(50) NULL,
Addr varchar(30) NULL,
Country varchar(8) NULL
)
INSERT INTO ContactInfo (CustID, Phone, Email, Addr, Country)
VALUES
(50, '850-225-8745', 'joshPorter@gmail.com', '1600 Pennsylvania', 'USA'),
(55, '850-333-8555', 'andrewBluefield@gmail.com', '34 Deronda Ave.', 'USA'),
(60, '555-887-5547', 'jackDonovan@gmail.com', '587 Student Ln.', 'India'),
(65, '585-325-4478', 'cindythatcher@gmail.com', '54 Royalton', 'UK'),
(70, '698-554-3365', 'gordonAcres@gmail.com', '128 Hamilton St.', 'UK'),
(75, '878-445-3432', 'gretchenHamilton@gmail.com', '344 Washington', 'USA'),
(80, '850-477-9987', 'brendaMaxwell@gmail.com', '67 Summerville', 'USA')
The two tables are linked through a CustID column.
So we have two tables to tell us everything we need to know about a customer. If we know a Customer’s ID, we can use it to see their first and last name in the Customers table, and also their phone number, email, and home address in the ContactInfo table.
Good, now you have some data set up.
The process for adding a new customer and their contact information:
If we want to add a new customer, what would we need to do? Well, 2 things:
- Insert a row into the Customers table, outlining their first and last name.
- Insert a corresponding row into the ContactInfo table, outlining their phone number, email, and home address.
The word “corresponding” is what makes this process a bit tricky. There’s also the fact that we need to add details for our customer in two places.
One thing to point out is the ‘CustID‘ column in the Customers table is generated automatically via an IDENTITY property. But the ‘CustID‘ column in ContactInfo is not. There, we need to outline that value manually when we do our INSERT, which means we are responsible for making sure it correctly matches up with the corresponding row in the Customers table.
This is becoming a bit complex. This is why once we figure out the code, we should save it as a stored procedure in the database.
Example of complex code
So, without further ado, here is an example of what our INSERT statements would need to look like if we wanted to add a new customer named Hank Hill, along with all his contact information:
See how much work that is? It would make our lives much easier if this code could be saved in the database and ran on demand.
Let’s break down what we have here:
1. INSERT into Customers
First, we have an INSERT into the Customers table which outlines the new customer’s first and last name
2. Capture the identity value
Then, we need to capture the identity value that was used for the CustID column in that last INSERT statement. To do that, we use the SCOPE_IDENTITY() system function. This function tells us the last identity value that was generated in the current scope. It’s very handy. We write that value to a variable called @identVal.
Remember, we need to capture this identity value because we need to use it in the next INSERT statement.
3. INSERT into ContactInfo
Next, we issue our second INSERT into the ContactInfo table, using the ID value we captured a moment ago which is saved in the @identVal variable.
The last thing we do is run a simple SELECT statement to show the user the new data that has been entered. Notice the SELECT statement does a JOIN between the Customers and ContactInfo tables to pull everything we have in one query.
Now again, I don’t know about you, but this seems like too much code to try to remember. What if we were adding new Customers every day, multiple times a day? Do we want to remember this query and re-write it every single time?
Not really.
So is there a better way to save this code, and call it very easily? You bet! Check this out:
All the logic to add a new customer is stored in my shiny new ‘addNewCustomer‘ object. Through a simple call, we can very easily add a new customer to our database.
The next sections discuss how to create the stored procedure and how to call it.
The CREATE PROCEDURE statement
It’s easy to create a stored procedure. Here is the general layout of how you would create a stored procedure:
CREATE PROCEDURE procedureName(<input/output parameters>) AS <stored procedure body>
For example, here is the CREATE PROCEDURE statement I used to create our new ‘addNewCustomer‘ stored procedure:
Let’s walk through what we have in this definition.
The CREATE PROCEDURE statement
Our CREATE PROCEDURE statement is where we outline the name of the stored procedure we want to make. This name can be anything you want it to be. The stored procedure will be put in the ‘dbo‘ schema by default if you don’t outline a schema name, which we haven’t.
Input Parameters
We have a total of 5 input parameters. These parameters are essentially variables that will be used throughout the body of the stored procedure. The names of the variables can be anything you want, but if you’re being a good developer, you will give them meaningful names!
- These parameters will hold the value we pass to them when we call the stored procedure. More on that idea when we get to the topic of how to call a stored procedure in a minute.
- Notice we need to specify a data type for each input parameter. Like any variable, they need to be given a data type.
Check out another tutorial I have that specifically talks about how to create a stored procedure with parameters to learn more:
Stored procedure with parameters: A guide for beginners
Default values for parameters
Notice the last three parameters (@phoneNumber, @emailAddr, and @mailAddr) are all set to a blank value:
This is how you specify a default value if a value was not passed for that parameter. It’s important to know that you can make some parameters optional, meaning a value does not need to be passed. In that case, the stored procedure will assign the default value to the parameter variable.
Again, in this case, the default value for those last three parameters is just a blank value.
We’ll look at an example in the next section where we talk about how to call a stored procedure.
Body of the stored procedure
Finally, we have the body of the stored procedure. This is nearly identical to the code we saw earlier. The only difference, of course, is we are using our input parameter variables instead of hard-coded values.
For example, for the first INSERT into Customers, we’re saying the FirstName column will be given whatever value was passed into the stored procedure for the @firstName parameter.
Notice in my layout, I said you can have input or output parameters. Here is that layout again to remind you:
CREATE PROCEDURE procedureName(<input/output parameters>)
It’s possible to have a stored procedure return a value or values back to the caller. The way you would do that is through output parameters. Again, I will show you what I mean in the next section.
Speaking of…
Calling a Stored Procedure
Enough teasing, let’s work this out.
Here is that call to our stored procedure again:
Let’s break this apart too:
When you call a stored procedure, you use the EXECUTE keyword. Optionally, you can say just EXEC, or you can leave out the word completely and just call the stored procedure by itself (NOT RECOMMENDED).
Then we say the name of the stored procedure we are executing. In our case, it’s our new ‘addNewCustomer‘ stored procedure.
Then we outline the values for our parameters of the stored procedure.
Understanding parameter values in the call to your stored procedure
Notice we do not wrap our parameters around parentheses like you would for a function. If you try, you will get error messages.
Notice in the call to our stored procedure, we outlined the name of the actual parameter, then an ‘=‘ sign, followed by the value we want to use for that parameter.
Example: @firstName = ‘Bobby’, @lastName = ‘Hill’, @phoneNumber = ‘657-344-2587’
You do not need to outline the actual parameter name (@firstName, @lastName, @phoneNumber) but it’s recommended that you do. Here is an example of calling our stored procedure without parameter names (and only parameter values):
I’ll say it again: This is not recommended. When you do this, you need to make sure you outline the values in the correct order in which they are defined in the stored procedure.
What can happen if you don’t outline parameter names
Here is an example of what I mean. The definition of the stored procedure has the first three parameters in the following order:
@firstName
@lastName
@phoneNumber
As long as we specify the names of our parameters when we call the stored procedure, we can mix and match them all day long. When the stored procedure finishes, the data will be how it ought to be.
Here is another call to the stored procedure, with the parameters “mixed and matched”:
Notice the parameters are all out of order from what’s defined in the stored procedure, but that’s ok. The final result set has the correct information.
But what if we took away the parameter names (leaving only the parameter values) and made the same call?
whoops
If you leave off the parameter names, you better be sure you outline your parameter values in the correct order!
Optional Parameters
I said earlier that you can flag certain parameters as being optional. In fact, that’s what I’ve been demonstrating in the last few screenshots. Let’s take another look at this screenshot:
Notice we don’t specify anything for the @emailAddr parameter or the @mailAddr parameter in this call. Since we didn’t specify anything for those parameters, SQL Server looks to see if those parameters have a default value, which they do, remember?:
Since we didn’t specify a value for those two parameters, SQL Server will use the default value for those two parameters, which is just a blank value.
On the same idea, we did choose to pass a value for the @phoneNumber parameter, so SQL Server will use that value instead of the default value.
Default values are ideal when you don’t want NULL to populate a column.
Output Parameters
Finally, we need to talk about OUTPUT parameters. In the definition of a stored procedure, you can have it return a value back to the caller. The most common way to do this is through an output parameter.
The topic of output parameters contains enough detail to justify it’s own tutorial, which I have have created. Take a look at the full tutorial on Stored Procedures with Parameters to learn more!
5. What are the limitations of Stored Procedures?
Stored procedures have much fewer limitations than functions.
You can do many things inside a stored procedure, including:
- Error handling
- Using temp tables
- Using dynamic SQL
- Running DML statements like SELECT, INSERT, UPDATE, and DELETE.
- Running DDL statements like CREATE, ALTER, DROP.
But, I digress.
Here is what you can’t do with a stored procedure:
You can’t query a stored procedure like you would a table (or an inline or multi statement table valued function). This is true even if your function returns a result set, like this silly stored procedure:
CREATE PROCEDURE seeAllCustomers AS SELECT * FROM Customers
This procedure just does a SELECT * FROM Customers. Thats it.
Here is an example of calling the stored procedure:
(yes, we still have that wonky row at the end)
When we call this stored procedure, sure, it gives us a result set. But the following attempted call plainly won’t work:
So again, the way to call a stored procedure is by using the EXECUTE statement, or just EXEC for short.
6. Tips and tricks
Here is a list of some helpful tips and tricks you should know when working with stored procedures in SQL Server:
Tip # 1: Use the ALTER PROCEDURE statement to change a procedure
We change a stored procedure by using the ALTER PROCEDURE statement, like this:
ALTER PROCEDURE procedureName(<input/output parameters>) AS <stored procedure body>
Here is an example of how you would run an ALTER PROCEDURE statement against our sample stored procedure:
When you ALTER a stored procedure, you need to completely redefine the entire stored procedure. You can’t just change a single part of it.
Tip # 2: You can abbreviate the word ‘PROCEDURE‘ when creating or altering a stored procedure
When creating or altering a stored procedure, you can abbreviate the word ‘PROCEDURE‘ to just ‘PROC‘ if you wanted to.
Tip # 3: Delete a stored procedure using the DROP PROCEDURE statement
Dropping a stored procedure is easy. Here’s the syntax:
DROP PROCEDURE <procedure name>
Notice you don’t need to outline any parameters or any other part of the definition.
Just like CREATE and ALTER, you could abbreviate the word ‘PROCEDURE‘ and just say ‘DROP PROC‘ if you wanted.
Tip # 4: Don’t start your stored procedure names with “SP”.
For example, the name “SP_AddNewCustomer” would not be a good name for a procedure.
When SQL Server see’s a stored procedure starting with ‘SP’, it thinks it’s a system stored procedure because that is what all system stored procedures start with. When SQL see’s ‘SP’, it will look for that procedure in the separate location where system stored procedures live, which is in the master database.
Upon not finding it, SQL will come back to your database and attempt to find the procedure there, where we know it was hiding all along. Save SQL from doing the unnecessary task of looking in the master database by not starting your stored procedure names with ‘SP’!
Next Steps:
Leave a comment if you found this tutorial helpful!
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!
