Join Gerry O'Brien for an in-depth discussion in this video Understanding stored procedures, part of Querying Microsoft SQL Server 2012.
After having looked at some of the queries that we've created throughout this course so far, you might be thinking that there should be a way for us to save this information to be able to use for future reference. And we can absolutely do that, and we can create something in our database known as a stored procedure. Stored procedures also serve to help us reduce network traffic from the client to the server. The store procedures also provide stronger security for our databases because we can specify permissions on the stored procedures in terms of what they can actually do.
They also give us a benefit of code reuse. They are easier to maintain and they improve the performance of the queries on the database because SQL server can optimize the store procedure in a way that makes sense for the data storage and the database itself. A stored procedure really consists of a group of one or more Transact-SQL statements. We can take a look at any of the queries that we've created so far in this course, and we can save that as a stored procedure. Another benefit of a stored procedure is, they can accept incoming parameters and return multiple values. So we can send.
Information into the stored procedure, and customize the data results that come back based on the parameters we provide. Again, they can return multiple values. So, even though it's a single stored procedure executing, it has the ability of returning multiple values in the results set. They can also contain programming statements for performing database operations. And some of these programming statements could be functions that we use within SQL server. We'll see some of those, really, as we look through some of the subsequent lessons following this one here.
And also, they can return a simple success or failure status to a calling application without needing to return a bunch of result sets. We have different types of stored procedures that exist in the database and that we can create ourselves. And what we're going to focus on is known as a user-defined stored procedure. That's the type that we'll create in this lesson. And really, they just simply represent standard, typical database queries. There are also temporary stored procedures, which are just another form of a user-defined stored procedure.
But instead of the stored procedure being stored in the database that it's referencing, where it's only temporary, it gets stored in the temp DB, which exists in all instances of SQL server. The third type is the system stored procedure. And in order to actually see what these are, you have to look into the internal hidden resource database, and typically you don't really mess around with these a whole lot, you certainly don't want to change them. The reason being is because they are used by the database engine for very specific database tasks, and you'll know that they are a system stored procedure because they start with an sp underscore.
And keep that in mind when you start creating your own store procedures, how the prefixes are utilized to determine the type of store procedure that you're looking at. And finally the fourth kind is an extended user defined stored procedure. And these are a little more complex because they allow the creation of external routines in programming languages such as C, C++, C#, or, you know, whatever your programming language is. And they typically exist in the context of dynamic link libraries, or DLLs, that would be available within that program execution.
Let's go out to SQL server and have a look at creating a stored procedure. This one will be relatively simple, it's not going to be a complex one, but it will be a stored procedure that accepts parameters. We'll see how to create the stored procedure itself, we'll see the statements that are used to actually build a structure of that stored procedure. And then we'll see a couple of examples on how to actually execute or call a stored procedure and pass in values to it. So what we're going to create here is a stored procedure. We will give it a name.
And you'll notice that in the name of the stored procedure we are prefixing it with a usp. Now again, depending on the naming conventions for wherever you're writing you're SQL code. You know, different companies have specific naming conventions. This might be usp underscore get employees by name, or simply usp for user stored procedure. Just as long as we remember to not use sp underscore so that there's no confusion about this potentially being a system stored procedure. In our stored procedure we are also going to use a couple of parameters.
So, we're going to accept the last name and a first name value. So, let's talk a little bit about the syntax this statement. In order to create the stored procedure we used the Create Procedure statement in SQL server. We are going to use human resources as the schema name so humanresources.usp, again, user sort procedure, GetEmployeesByName. So, it's a very well thought out name, GetEmployeesByName is fairly self explanatory for what this stored procedure will do.
Make sure when you name your stored procedures that they are kind of explanatory. Again, remember, developers may be accessing these stored procedures through code, and the more explanatory the name is the better off that they will be able to understand what the intention of that stored procedure is. After we declare the CREATE PROCEDURE statement and give it a name, then we can go ahead and specify in a comma separated list the parameters that we want to be able to accept data into for the stored procedure. So in this case, we're talking about last name and first name, and note we prefixed these with the at symbol.
And the at symbol is used to denote this as a variable. So, in other words, SQL Server looks at the @LastName and the @FirstName and knows that these are variables that will accept data that's passed in. Now, because we have to pass in data to this in the stored procedure, and we really want our stored procedures to execute without error, we also specify. The data type that we expect these values to use. Now again, when we take a look at the values stored in the database, it's imperative that you understand the database structure, the data types used in the database, so that you can ensure you're accepting the right parameters with the proper data types.
So, we're going to pull out of, Get Employees by Name, in which case it means we're. Going to look at the employee's table and in this case here, we're going to pull first name, last name and department. So if we look at the HumanResources.Employee table and we are looking for the actual first name and last name, then we're looking to see does it exist the HumanResourcesEmployee table? Well, it doesn't right? So that again, right after that, we're thinking. Well, where do we get this data from, how is our data structured? We have to actually look into, in order to get this information, you can even see where we're pulling it from.
In this case, believe it or not, we're going to pull from a view. So in essence, what we're doing with this stored procedure is reducing complexity quite a bit. So if we take a look at the last name and first name for an employee. We just saw that it didn't really exist in the Employee table. So, where are we getting it from. How can we get this information? We know that we could go in and we could create the join, if you will, between the person table, which would get the first name and last name. And then relate it back to the employee table to that business entity ID to get any other pertinent information that we're looking for.
But instead knowing again how our data base is structured you'll notice that, I'm just going to collapse these so it makes it a little bit easier for us to see, you'll notice that we're pulling from a view, the view, EmployeeDepartmentHistory. So if we expand the view, and we look at employee department history here as the view. Just like a table, we can expand it and look at the values that are in here. So, here's where our first name and our last name values are. And it's important to note, we've used the nvarchar 50 for both of these input parameters, because that's the data type that is being stored in the database itself.
So, really that was a long way around telling you where we're getting the information from. But it was imperative for you to understand how the values are related to the parameters in this stored procedure. These must match; if they don't and you have a mismatch, your stored procedure will generate errors. So create procedure, give it a name, list the parameters. And then use this keyword As again, and then we can start to create the SQL Select statement that will build the information that the store procedure to use the return the data.
Now it's important that we execute this statement as well. The set no count on. Is actually a command that tells SQL in the store procedure aspect, don't return a row count. So if we were to execute this store procedure not only will we get back the information we're looking for such as first name, last name, department. But we would also get a roll count and we don't really want to see that, so we're just setting no counts so that it doesn't return the roll count. Now here's our actual query portion. So this is kind of the meat and potatoes of what our SQL stored procedure will consist of.
It's a standard Select statement. So, we're going to select the first name, last name and department from the Human Resource's ViewEmployeeDepartmentHistory. Now, the Where clause is where we actually make use of these variable names that we declared up here. And we say where first name equals in the @FirstName variable. So basically what this means is we're saying when the use of this stored procedure passes in first name, we want to use that variable at first name and pass it into the Where clause.
For the first name field. Likewise with the last name, so this is the parameter that will accept the value that the user of our store procedure will pass in. This is the field that it will be compared against, using the equal operator in, basically in this way here we can customize, or generate this query, if you will, on the fly to the store procedure. By passing in the last name and first name that we want. And then of course we ended here with, and then end date is null, meaning that we don't want to be focused on an end date, which is here, and we want to say the end date is null, and it does accept null in the database. We'll see an example of, when we execute. How this actually plays out from the results coming from this view. And then, because we want to create this stored procedure as a batch, we end off with the Go statement itself. So, let's go ahead and execute this create procedure statement. So we will create the stored procedure in the database. And we get a command, completed successfully. Which means if we come back to our database, and look under the programmability folder, this is where stored procedures exist. This is where you'll find functions and triggers, etcetera. We called our stored procedure uspGetEmployeesByName. And, again, if we look in here, because we didn't have this expanded, we don't need to do the refresh. We see the HumanResources.uspGetWEmployeesByName stored procedure. So the command executed correctly and the store procedure now exists in our database. Great, but how do we call that? Now, it depends on whether you are calling it from within SQL Server or whether you are calling it from within a programming language. But typically, you will issue a command such as execute and you are going to execute the name of the store procedure and you will provide the necessary parameters. So we are going to execute and we use the store procedure name and then we pass in the comma separated lists of values in the parameters. Now again, because we're using Unicode, we should also have the n specified at the head of the first name as well. Again, note that we're not specifying the parameter values here, but we know that. Looking at where we created it. The last name is the first parameter and the first name is the last parameter, and I didn't put those in that order to confuse you, so I apologize if it does it, but we're looking for the last name and the first name. So, that's the way we've actually passed these values in. So if we execute this stored procedure we're using the parameter values in their ordinal positions within the stored procedure. So if we execute it we see that we do indeed pull back Pilar, Ackerman and the department where Pilar works is Shipping and Receiving. SQL server also allows us to clearly specify the names, so again using the ordinal position sometimes can run you into trouble if you put this out of order. You're not going to get the results back that you would kind of hope you were going to get back because of the fact that we've put them in the wrong order, so SQL Server doesn't know that we've put them in the wrong order. So it will simply return what it thinks are the correct results. Which unfortunately, would be no results in this case. So it's far better to include the variable or the parameter names. In your execute statement, so that we are very clear on what we are passing in. Also you don't have to type the entire Execute because SQL provides with a shorthand called exact, which means exactly the same thing. So if we execute this statement, we will get precisely the same results returned. So it's just a way of showing two different ways of calling your store procedure. Really taking a look at this, we've seen how to create a new store procedures in SQL Server and sometimes creating a store procedure in SQL Server Management Studio is a little bit easier due to the templates that are available. You've got the ability to right-click and, you know, generate store procedures, but creating them with transact SQL statements really allows you to see how the create procedure commands work. And gives you really fine tuned control over how you're creating your procedures themselves. You'll still write the actual query components to return your records desired, even if you're using the templates in SQL. Keep in mind, your stored procedures really provide much better performance than adhoc queries, even though we're basically, in this example, passing in unknown. Last name and first name, so the stored procedure doesn't know what those are at first. But SQL still has the opportunity to optimize the, select statements and the stored procedures as much as possible. Which increases performance for your database. So that was a quick look at stored procedures, and we'll get a good look at some other programmability functions within SQL Server in subsequent lessons.
- Writing SELECT queries
- Querying multiple tables
- Filtering text and duplicates
- Sorting and grouping query results
- Using SQL Server's built-in functions
- Writing subqueries
- Using common table expressions
- Programming with T-SQL
- Interpreting query performance data