Stored procedures (SPs) are one more powerful database object we have at our disposal. They can help us handle many tasks and improve performance and security. Today, we’ll take a look at simple SPs and show, on examples, how to use them.

The Model

As always, at the start of the article, we’ll remind ourselves of the data model we’re using.

In this article, we’ll create simple stored procedures that will use a single table, and that will be the customer table.

What Are Stored Procedures

Stored procedures (SPs) in SQL Server are just like procedures/routines in other DBMSs or programming languages. Each procedure has one or more statements. In our case, these are SQL statements. So, you can write a procedure that will – insert new data, update or delete existing, retrieve data using the SELECT statement. And even better, you can combine more (different statements) in the stored procedures. Also, inside the procedure, you can call another SP, function, use the IF statement, etc. Therefore, it’s pretty obvious SP can do much more than a single select query.

The main idea is to write down the procedure performing all the operations we want, and later, when needed, call this procedure using parameters. Therefore, an SP for the end-user would be like a black box, receiving input and returning the output.

Stored Procedures – Simple Example

Let’s now take a look at a few simple SPs. For the first example, we’ll create an SP returning all values from the customer table. To do that, we’ll use the following code:

I want to emphasize a couple of things here:

• We’ve used the DROP PROCEDURE IF EXISTS p_customer_all; statement in the first line. This is nice practice, especially when you’re creating scripts you want to work always, no matter the state of the database. The command DROP PROCEDURE p_customer_all; would delete the procedure with the given name. Still, if the procedure wasn’t already created in the database, this would result in an error. Therefore, adding IF EXISTS prevents this from happening. This row generally says – I will delete this procedure if it’s on the server, and if it is not present, OK, do nothing
• The word GO is inserted between two SQL statements in situations like this one
• The name of our procedure is p_customer_all. The reason for that is as follows – “p” is for the procedure, followed by the table name (customer) and the action we’ll use this procedure for (return all)
• The body of the procedure is just a simple select statement returning all rows from this table

After the procedure is created, you can see it in the Object Explorer, under Programmability -> Stored Procedures.

Let’s now call/execute our SP.

To do this, we’ll use the syntax: EXEC procedure_name <parameters if any>;. So, our statement is:

The result is shown in the picture below:

As you can see, this procedure is pretty simple, and it did exactly the same job, the simple select would do. While this doesn’t seem to have benefits, actually, it does. Still, these benefits become even more visible on more complex procedures. We’ll talk about the advantages of using SPs in a minute.

Stored Procedures – More Complex Examples

The previous example was pretty simple, but still nicely shows what SPs can do. Besides that, we can easily create SPs to get only one row, insert new or delete/updated existing row. We’ll show 3 examples – retrieving row by id, inserting new row & deleting an existing row. We’ll use the same naming convention rule, we’ve used in the previous example (p_table_name_action_name).

For the procedure that will return only one row based on the id, the code is:

The new moment here is that we pass the parameter to the procedure. We can pass one or more parameters. We’ll list them all after the procedure name in the CREATE PROCEDURE line (CREATE PROCEDURE p_customer (@id INT)).

Now we’re ready to execute our second procedure:

The result is, as expected, all details for the customer with id = 4. Please notice that we’ve listed parameter(s) without “(“ and “)” after the procedure name in the EXEC line.

Let’s now create a procedure that will insert a new customer in the table.

The important things to notice here are:

• We’ve used more than 1 parameter in this procedure
• For the value ts_inserted, we’ve used the SYSDATETIME() function to store the current time

After executing the procedure, using the statement:

the new row was added. We’ll check what is in the table by calling the first procedure we’ve created:

The last procedure, we’ll analyze today is the one to delete a row using the id passed as parameter. Let’s create the procedure first.

Once again, we’ve followed the same naming convention when giving the name to our procedure. We pass only 1 parameter and that is the id of the row to delete. Let’s call the procedure now:

This deleted the row with id 6. Let’s check it again, using our first procedure:

We’ve seen 4 examples of how we could use SPs to perform simple database operations. In upcoming articles, we’ll go with more complex stored procedures. But before we do that, let’s comment on the advantages SPs have.

Advantages of Using Stored Procedures

SPs have a lot of advantages. I’ll try to list the most important ones:

• Modular programming – If you decide to put all logic inside SPs, you’ll be able to easily create/identify modules/parts of your code in charge of different business operations in your system. This will require using the good naming convention and stick to the internal rules, but the benefits are really great. When you need to change something, you will be able to find the related code faster. When you change that code (SP), the change shall be immediately visible at all places where this SP is called
• Better performance – Stored procedures are parsed and optimized after they are created. Since they are stored, there is no need to parse and optimize them again like that would be the case when not using them. This shall definitely spare some time when executing queries inside the SP
• Reducing network traffic – This might not be so important as others, but is still an advantage. When you call an SP, you’ll pass its’ name and parameters. Otherwise, you’ll need to send all the lines of code. In case the SP is pretty complex, this would have a larger impact
• Security – This one is very important. Just as with other database objects, you can define who can access them and how he can use these objects. You can grant the user permission to execute an SP, even if he doesn’t have permission to use all tables in that procedure. That way, you’ll be able to limit users to use only these objects you want them to use. Besides that, the potential attacker won’t be able to see the structure of your database in the code – he’ll only see the name of the SP you’re calling

Conclusion

Today we took a look at another very important database object we have on the disposal – stored procedure. They offer a number of advantages. Maybe the biggest disadvantage would be that you need to take care of a large number of procedures and have a procedure for everything – from the simplest to very complex tasks. Still, a good naming convention and internal organization could easily turn this disadvantage into an advantage (by forcing you to follow the same standards and principles in the whole system + simplifying the documentation and, therefore, greatly increasing the chance that you’ll generate it).

Table of contents

• Author
• Recent Posts

Emil Drkusic

Emil is a database professional with 10+ years of experience in everything related to databases. During the years, he worked in the IT and finance industry and now works as a freelancer.

His past and present engagements vary from database design and coding to teaching, consulting, and writing about databases. Also not to forget, BI, creating algorithms, chess, philately, 2 dogs, 2 cats, 1 wife, 1 baby...

You can find him on LinkedIn

View all posts by Emil Drkusic

Latest posts by Emil Drkusic (see all)

• Learn SQL: How to prevent SQL Injection attacks - May 17, 2021
• Learn SQL: Dynamic SQL - March 3, 2021
• Learn SQL: SQL Injection - November 2, 2020