From the course: Complete Guide to Advanced SQL Server

Write and execute a stored procedure - SQL Server Tutorial

From the course: Complete Guide to Advanced SQL Server

Start my 1-month free trial Buy for my team

Write and execute a stored procedure

“

After views and user-defined functions, the next step up in programmability object capabilities is a stored procedure. Stored procedures can perform many of the same tasks as views and functions. They can perform SELECT queries against the database just like a view. They can also take input parameters like a function. What sets them apart is that stored procedures can perform multiple tasks in a single routine, and they can modify the data that's stored in your database. To create and manage stored procedures, you'll use the same CREATE, ALTER, or DROP syntax that we use for other objects inside of the database. Just like a function and a view, you'll name the procedure using a two-part naming convention that will place it in a specific schema of the database. Here, I'm creating a stored procedure inside of the application schema of the Wide World Importers database. I'm going to call this procedure "uspViewEmployees." Now, we haven't talked about naming conventions in this course yet, but it's a good concept to always keep in mind when developing a database. So let's take a quick aside and think for a moment about how we name our objects. As you develop your database, it's likely that you'll ultimately have lots of different programmability objects that all perform different tasks. Because they all get saved into database schemas, it can start to get confusing when you see them in code. For example, if you see Warehouse.InventoryLevels, you're not able to instantly recognize whether this is a table, a view, a function, or a stored procedure. So a consistent naming convention will help developers understand what type of objects are being referenced when they see them in code. Most often this takes the form of a consistently applied prefix. Now, your organization might already have a naming convention in place if you're working in an existing database. If that's the case, then you should stick with whatever pattern has already been adopted. If you're working in your own projects, then here are some common prefixes that you can use. For stored procedures, it's common to use the usp prefix before the name or usp_. This abbreviation stands for user stored procedure. The prefix sp_ is used by SQL Servers own built-in stored procedures, and should not be used for any procedures that you create yourself. For functions, it's common to see the ufn or fn_ prefix before the function name. And for views, consider prefixing the name with a single lowercase v or vw_. When you apply naming conventions consistently across your databases design, it makes it much easier to quickly understand what's going on. Okay. With that aside out of the way, we can get back to writing our stored procedure. We have the create or alter command followed by the procedures name here on Line Number 6. Next, we have the "AS" keyword. Then we'll have any actions that you want the procedure to perform. This procedure is performing a SELECT query by pulling a couple of columns from the Application.People table. We're then filtering the results to just the rows that correspond to employees. In this specific example, the stored procedure is acting much like a view. Let's go ahead and highlight Line 6 through 11 and we'll execute it to create the stored procedure on our system. Now, to run the procedure, the syntax is a little bit different than using a view or a function. We can see the syntax that we'll use on Line 14. Stored procedures don't get incorporated into a SELECT statement. They are their own standalone thing. We use the execute command, or you can abbreviate it to just exec and drop the ute part of it, and we'll execute and then we just name the stored procedure. So Application.uspViewEmployees. Let's go ahead and highlight Line 14 and execute it. And there is the result. Stored procedures give us another opportunity to place an abstraction layer between the end user and the database's actual structure. This can help secure the database and make maintenance and changes easier to accommodate. If you change a table or a column name, you can update the procedure to match the new names, and end users won't need to update their applications to interface with the updated design. In the case of our procedure here, if you want to use the procedure to mask column names, then it's just a matter of adding column aliases to our SELECT statement. I've got an example of that starting on Line Number 18. So here, we're going to alter the same procedure name, but I'm going to start adding in column aliases. So the PersonID column will get renamed to IDNumber, the FullName column will get renamed to just Name, the IsEmployee column can be handled a little bit differently. We know because of the WHERE clause that the IsEmployee column will always contain a one for every row that the stored procedure returns. So we can actually just hardcode that into the procedure and clarify what that means. I'll just add the text "Employee" in a new column that I'll call Status. Then for the IsSalesperson column, we can add a CASE statement to, again, clarify what the zero or the one numeric code actually means. So here, I'll say when the case is IsSalesperson equal to one, then we'll put in the text "Salesperson." And when salesperson is equal to zero, then we'll put in the text "Not Salesperson." We'll call this new column "Position." We'll still select all of these rows from the Application.People table, and we'll still apply the same WHERE clause. So WHERE IsEmployee is equal to one. Let's highlight Lines 18 through 28 and execute it to alter the procedures configuration. And now, we can run it again just by executing the same line up here on Number 14. So I'll say, EXEC Application.uspViewEmployees. And now here in the results, I can see the updated look. So now that the stored procedure has been altered, running it using the exact same command as before gives us an improved result. So if this EXECUTE statement was incorporated into an end user application, you can see how you can easily change the output that the application receives without having to modify the application itself. So that's an example of how a stored procedure can mimic the output of a view. Here's something that a view can't do. A view cannot perform multiple actions. In the next example, starting on Line Number 31, we're going to perform three SELECT statements inside of a single procedure. We'll just pull the first row from each of these tables: Application.People, Sales.Customers, and Warehouse.Colors. We'll create the procedure and I'll put it in the application schema and I'll call it uspViewData. Let's create the procedure, and then I'll execute Application.uspViewData. And here, we get three different result sets. This returns the three rows that I asked for in a single command. And this illustrates one of the performance benefits of using stored procedures. I only had to send one command, and the server was able to perform three different actions. If I were doing this with views or a standard SELECT query, I'd have to send three separate commands over the network to the server to get the same result. For complex procedures that may contain hundreds of actions, this could dramatically improve the performance of the database.

Contents