Understanding stored procedures

- Understanding Stored Procedures. So, what is a Stored Procedure? Well, the name is descriptive. A Procedure in Computer Science is a block of code that does something. It does a "procedure," and we call these Stored Procedures because the definition of that block of code is stored within SQL. Even though they're stored within SQL, you can call a stored procedure, or commonly known as "stored procs," a Stored Procedure can be called from anywhere where the context, that is the application, et cetera, can pass in the correct security information.

So, if you have permission to call the stored proc, you can call it from within SQL, or externally. They can accept a parameter, and they can, and often do, return a result set, and, also a status code. Here's the syntax. Create Procedure, and then the schema, Human Resources, dot, and then the name of the Stored Procedure. USP stands for User Stored Procedure, and that's just a convention, we tend to do that, and then the name of it.

The parameters, it has two, Last Name and First Name, and they are typed of nvarchar 50's. Now the code is in the block, SET NOCOUNT ON, we don't want to see how many results come back. So, FirstName, LastName department, you can see it, from the view Employee Department History, where the FirstName is first name, LastName is last name and End Date equals Null. That is, they're still here. There's three ways we're executing it, you see the first. Just Execute and pass into parameter.

The second we're passing it in is @LastName equals, and @FirstName equals, and then we just flipped it in the third example at the bottom. By the way, this example is straight out of Microsoft Developer Network, which is an excellent resource I would advocate that you use. The Advantages. They are stored within SQL. Because the code is stored within SQL, it is secure. So, our procedures have faster execution because on first execution, the execution plan is cached.

On subsequent execution plans, the plan is reused. We reduce errors because the code is encapsulated. When to use Stored Procedures. If you have a two-tier system, and a two-tier system is kind of old school. Two-tier system means you've got a front end application of some type. Access, you wrote a little web application, you've got a desktop application, whatever, and you're talking directly to the database.

In that situation, your stored procedures will implement your business rules. In an N tier, the old three-tier, and then it went to four-tier, and now it's N-tier because we have no idea where this will end up. We probably want to use Stored Procedures in a limited capacity, and I say that because when we go into N tier, the business tier, which sits in the middle between presentation and data, so the middle tier, the business tier, the middle tier, or the rules tier, whatever you want to call it, normally contains the code that will be modified as business rules change.

Therefore, the code that we encapsulate into a Stored Procedure is in the middle tier in this situation. And then, you want a limited use of Stored Procedures.