Stored Procedures in SQL Server are a great feature that can help you build
logic into your database and improve data quality.
In this video, you’ll learn what a stored procedure is,
and see some simple examples of creating stored procedures in SQL Server. We’ll
see small examples that insert, update, delete, and select records in a database.
A stored procedure is a collection of SQL statements that can be run.
It’s an object that is stored in the database. You can run the stored procedure after you create it,
and it can be run by other users or applications that can access the database.
Let’s start by creating a simple table. I’ve got a link for this
script on my GitHub repository in the description below if you want to copy it.
We’re going to create a new table called customer. It has three fields: an ID,
a name, and an is_active field to indicate if the customer is active or inactive.
We’ve got a couple of Insert statements here as well, to add some data to the table.
This is just to get us started, so our stored procedures will return some data.
We can run both of these statements, the Create Table and Insert, and they should be successful.
Now, let’s create our first stored procedure.
We’ll create a stored procedure that selects some data from a table.
To create a procedure, we write the Create Procedure command.
SQL is not case-sensitive, so you can enter it in either upper case or lower case, it’s up to you.
Next, we need to provide a name for our new procedure. Given that we will be selecting
data from our newly-created customer table, let’s call it selectCustomer.
We then add the keyword AS, which indicates we are about to define what the procedure is.
Next, we add the word BEGIN. I like to do this on a new line to make it more readable.
After the BEGIN keyword, we’ll leave a gap to add in the code that we want to run.
Then we add the END keyword on a new line.
Inside the BEGIN and END keyword is where we can add the code for the procedure to run.
We want to select some data from the customer table, so let’s write a query for that.
We’ll write Select id, name, is_active from customer, and add a semicolon.
So, this is our stored procedure. When we run this Create Procedure statement,
a new stored procedure is created on the database.
Let’s run this now. If you’re using SQL Server Management Studio,
you might see a bunch of red underlines here. But we’ll try to run it anyway.
When we run this script, we can see it has been created successfully.
Now we have created the stored procedure, the next step is to run it.
To do this, we use the EXEC command or the EXECUTE
command. I’ll use EXEC because it’s shorter, but you can use either one.
Type EXEC, then the procedure name, then a semicolon.
Run this command. The procedure will be run.
You’ll see the results at the bottom of the window. It looks just like the results of a
Select query. This is because our stored procedure ran a Select query inside it.
So that’s how you can create a simple stored procedure to select data.
Let’s see a few more examples.
Now we will create a stored procedure to insert data.
It starts the same way as the first example. We write Create Procedure. Then we give it a name.
Let’s say we want to insert a new customer record with this procedure,
so we can call it insertCustomer.
We then have the BEGIN on a new line, and the END on a few lines below that.
What do we put inside our procedure?
We want to add an Insert statement here. So, we enter Insert Into Customer,
then specify the columns, which are id, name, and is_active.
We then specify values.
After the bracket is where we add in the values we want to insert.
But, what do we insert? How do we enter the values we want to insert?
This is where parameters can be used. A parameter is an input to a stored procedure that is used
within the code in the stored procedure. The value is provided when the procedure is run.
For us, this means we can provide the values for the insert statement
as parameters when we run the procedure.
To do this, let’s add one parameter. We add a parameter by adding brackets
after the name of the procedure. Inside the brackets, we specify a few things.
First, we add the @ symbol. This indicates that this is a parameter.
Next, we have the name of the parameter. We’ll call ours customerName,
just so we don’t get it confused with the name column in the table.
Finally, we have the data type. This is a name of the customer,
so we’ll give it a varchar type and a length that matches the name column of 100.
We close the brackets before the begin keyword.
Now we have specified a parameter, we can use this in our Insert statement.
For the ID column, ideally we would have this auto-generated or provided as a parameter,
but to keep it simple for now we’ll just add a static value of 4.
For the name, we specify the name of the parameter which was customerName.
For the is_active, we can set that to 1.
Now our procedure is complete.
We can run this script and the procedure is created.
If you see some red lines here, or try to run the query and get an error about
the code being the only statement in the batch, this is because SQL Server expects
you to end the batch of statements before running another one in the same script.
If this was a single script, you could add a forward slash character to end the batch. Or,
we could select just the statement we are running and run that, which should work.
Let’s call this stored procedure now.
We can write an Exec statement, then the name of the procedure.
Let’s run this command.
When we do, we get this error here.
What happened? This error is shown because we ran the stored procedure
that expected a parameter, but we did not provide a parameter.
Let’s fix that. To add a parameter when calling a procedure, we add a space after
the procedure name. Next, we specify the value to use for the parameter.
Because it’s a string, we enclose it in quotes.
We don’t enclose this in brackets like in some
other languages. We just have a space then the parameters.
Now we can run the command again.
We can see here the command is successful. It shows a green tick and says 1 rows affected.
There is no Select output shown because the procedure did not include any Select statements.
We can run a Select query here, just a simple one, and we can see the results.
We can see our new value is inserted into the table.
So that’s how you can create a procedure that inserts data. There’s a whole lot of logic
you could and should add to a procedure like this, but that’s the basic version.
What else can you do?
Let’s see an example of a stored procedure that updates some data.
We start with the same structure as the previous example, with the create
procedure statement. We’ll call it updateCustomer.
What parameters do we need? We need to know two things when we are updating a record:
what the new value should be, and what the criteria is for the rows to update.
Let’s say we want to update the customer name, so the new customer name value is one parameter.
And let’s say we want to work out which customer to update based on the ID,
so that will be our second parameter. We separate them in the parameter section here with a comma.
We’ve separated them onto different lines to make it a little easier to read.
Inside our procedure, we write our Update statement.
This Update statement will update the customer name to the value provided as a parameter. The
Where clause will allow us to update only the customer records with the provided ID.
Our procedure is now written. We can run this script and the procedure is created.
Now, let’s call it. We can call it in the same way as the other procedures.
This procedure is a little different as we have two parameters.
We add both parameters, separated by a comma. We have a name of ABC Exports and an ID of 2.
We can run this statement, and can see it was successful.
How can we see what the data looks like?
We can run a Select query. We’ll run the same one as before to see all data in the table.
In our results, we can see that the record we specified in the update procedure has the new
value we specified. The record with an ID of 2 now has a name of ABC Exports.
The final example we’ll cover is how to delete a row in a stored procedure.
It works in the same way as other procedures.
We’ll start with an empty procedure like this, which we’ve called deleteCustomer.
To delete a row, we need to know the criteria for the row to be deleted.
Let’s say we want to specify an ID, and delete that row. So we add that as a parameter.
Now, inside our procedure, we write a Delete statement.
We delete from our customer table, and add a where clause that deletes based on the ID parameter.
The procedure is now written. Let’s run the script, and the procedure is created.
Let’s run the procedure. We can run it in a similar way to the other procedures,
using the EXEC statement. We specify a parameter here.
In this example, we’ll provide an ID for a row that does not exist, just to see what happens.
When we run this command, we can see the procedure runs. There are no errors, but in the output panel
in SSMS it shows us that 0 rows were affected. This indicates that no rows were deleted.
Let’s call the procedure again, but we’ll use an ID for a record that exists.
We get the same success message, but the response says that 1 row was deleted.
Now, we’ll run a select query to see the data. We can see the results here, and see that the
ID we specified is no longer found in the table. This means our delete statement is successful.
So we’ve seen some basic examples of stored procedures in SQL Server. What else can you do?
You may want to add some error-handling logic to your procedure, such as checking
if a row exists before you update or delete it.
You may also want to add some validation on the input parameters,
such as if a value is not empty or matches a certain format.
There are a lot of other things you can do in the procedure as well.
Hopefully, this video helps you with your understanding of stored procedures in SQL Server.
If you have any questions or requests, leave a comment below.
If you want to learn more about database design and SQL, visit my website at databasestar.com.
If you liked this video, consider subscribing to my channel.
Thanks for watching.