SQL Stored Procedures Tutorial – Look inside the SQL Queries Course

SQL stored procedures helps a programmer maintain consistent implementation of procedures across applications. Stored procedures contain code that represents commonly performed tasks that are designed and then coded and stored in a procedure that can be used to perform the task over and over again. The SQL Queries 101 course teaches the beginner SQL programmer or administrator how to create basic SQL queries and write and compile other code statements that allow for the development and administration of SQL Servers.

This blog will take you through one of the step-by-step tutorials in the SQL Queries 101 course. It will teach you how to create stored procedures, how to join tables in SQL, and how to access and manipulate your stored procedures to create new stored procedures based on the original procedures.

Let’s begin with an example database called JProCo.  The various labs and tutorials contained in the course allow you to set up your own database to follow the step-by-step instructions to help you learn how to create stored procedures.

This report will join the employee table to the location table. To create the first query for the report, your need to open a new query window and then type ‘SELECT all fields, all fields are selected by typing the ‘*’ symbol. You need to type the name of the table, in this case the ‘Employee’ table. So you need to type:

We want to know where the employees are located, so we will say INNER JOIN Location As L on E.LocationID = L.LocationID.

Looking for more information on SQL queries and how to use them? Try a beginning SQL server guide for step-by-step tutorials on how SQL queries and joins work.

The result of our query is displayed but there are too many fields included in our results. All we want is first name, last name, city and state. We need to select specific information from the tables, rather than using the ‘*’ to select all of the fields.

So, we’ll say E.FirstName, E.LastName, L.City, L.State. Let’s run that. Runs good. Just for good measure, we’ll delimit this with square brackets and run it again.

Well, now we want to narrow it down to where L.State is equal to Washington.

Execute the query. All right, there’s the report we want.

Now, let’s say this query is going to be used frequently so we want to save this procedure for later use. To create the stored procedure, you need to indent the code and then put something above it that says create procedure and then call this ‘GetWashingtonEmployees.’ On the next line you need to add as to complete the syntax required to create the procedure and then ‘go’ at the end of the procedure. Once the procedure has been created we can try to run it. This is what the procedure now looks like:

To run the procedure, open a brand new query window and type, ‘EXEC GetWashingtonEmployees’. EXEC is short for execute and allows the procedure to run.

Run that, and the name refers to all the codes saved inside of it and you get your report:

Okay, let’s close this window and look at the code that created this stored procedure. Close all the open windows so that no query windows are open.

In your object explorer, expand your JProCo database, expand program ability, expand stored procedures and you will see we have this procedure called, ‘GetWashingtonEmployees.’

So, the procedure has been saved. The procedure is now part of SQL server, and anytime we want to run it, we can just execute this exact stored procedure and the report comes up.

If you wanted to see what code created this stored procedure then you can right click and select ‘script create to new window’ and it shows you the code that went into the creation of this stored procedure.

This query finds first name, last name, city, state, where it all equals Washington.

Now let’s build a second stored procedure that is very similar to this stored procedure. To create a new procedure based on this one you need to select the query inside of it and then copy and paste that code to a new query window. Now, you will change the code slightly so it selects all non-Washington employees by saying not equal to Washington or ‘!=’

And there are the three employees who don’t work in Washington.

Now you need to repeat the process and say, ‘create procedure get non-Washington employees as’. So type:

Run that, and now let’s go ahead and execute that stored procedure. To execute the procedure, open a new query window, execute get non-Washington employees. There we go. Does it work if we put it in its own window? Yes, it does.

Now let’s verify that you can find the ‘get non-Washington employees’ procedure in your stored procedure folder. You might only see it after you refresh the screen, so be sure to refresh.

There it is.

Let us delete that procedure so we can recreate it in a slightly different way. To delete the procedure, right click the procedure and select “delete”.

Now go back to the code that created the procedure.

We will use a shortened version to create the procedure. If you don’t like typing out the whole word procedure, you can just shorten all of your statements to ‘create proc’ and the name of the stored procedure.

Now you can run that, notice that the procedure is back.

Open the query window that calls on stored procedure, and notice that it runs just as it did before.

SQL Stored Procedures are Simple to Create

Creating SQL stored procedures can help you save a lot of time. SQL queries that are used often should be stored as stored procedures to save you time and effort. This tutorial was based on a look inside the SQL Queries 101 course, designed to teach a beginner everything you need to know about SQL queries. The course includes step-by-step lectures on SQL queries, definitions, how to manipulate data and transactions. It also offers lessons on security and offers various workplace tips and the course will help you become proficient at the SQL code you need to know to write SQL queries.