SQL Server 2014 - Stored Procedures
Stored procedures can improve security and performance, as well as increase productivity by facilitating modular development.
In SQL Server, stored procedures are much more powerful than views. Views allow us to run a complex query by simply doing a
SELECT against the view itself, which by the way, is a pretty cool thing. But views don't really provide us with the ability to provide business logic within our code. For example, views don't allow us to add conditions to the code (such as
IF statements). Fortunately, that's where stored procedures come in.
What Is A Stored Procedure?
A stored procedure is a group of SQL statements compiled into one. Similar to what I was saying on the SQL scripts page, where I mentioned that you could run many SQL scripts as one.
However, a stored procedure is more than just a long script. It's a script that has been saved in SQL Server specifically under the Stored Procedures node. A stored procedure will typically contain some business logic. For example, a stored procedure can accept parameters that are passed to it and test against those parameters using
IF statements. Eg, if the parameter is one value, do this, if it's another value, do that.
Their ability to contain business logic makes stored procedures a powerful part of SQL Server. Stored procedures can improve performance in an application, because the stored procedure is parsed and optimized as soon as it's created, and then stored in memory. Running a conditional query via stored procedure can be extremely quick - compared to an application that sends a query across the network, to the SQL Server, then has all the data returned to it across the network so it can filter through it, and pick out only the records it's interested in.
Benefits of Stored Procedures
Here are some of the main benefits in using stored procedures:
|Modular programming||You can write a stored procedure once, then call it again and again, from different parts of an application (and even from multiple applications).|
|Performance||Stored procedures provide faster code execution and reduce network traffic.
|Security||Users can execute a stored procedure without needing to execute any of the statements directly. Therefore, a stored procedure can provide advanced database functionality for users who wouldn't normally have access to these tasks, but this functionality is made available in a tightly controlled way.|
Create a Stored Procedure
To create a stored procedure, you need to use the
CREATE PROCEDURE statement, followed by the code that makes up the stored procedure. If your stored procedure is going to accept parameters, they need to be included after the name.
The following code creates a stored procedure called "LatestTasks". It accepts a parameter called
@Count. Whenever you call this stored procedure, you pass the @Count parameter along with a number, which determines how many rows you want returned. Here's the code:
Run this code in the SQL Server Management Studio and you'll see that it gets created under the Stored Procedures node as "LatestTasks".
In SQL Server 2014, you can create a stored procedure by right-clicking on the Stored Procedures node/folder and selecting Stored Procedure.... This will open a template that's ready to be populated with your own specific procedure.
Execute a Stored Procedure
Now that you've created your stored procedure, any time you want to execute it, you need to call it using either
EXEC. If the stored procedure requires parameters you provide those after the procedure name. Like this:
In the following example, we execute the stored procedure twice at the same time. The first time we call it, we pass in a
@Count of 3. The second time we pass in a value of 5.
The screenshot shows that by passing the paramater (and a value), the stored procedure returns results based on the value that we provide. The top result set returns 3 rows because we passed in a value of 3. The second result set returns 5 rows because we provided a value of 5:
Using The GUI
You can also use the graphical user interface to execute a stored procedure.
- Using the Object Explorer, navigate to the stored procedure
- Right click on the stored procedure and select Execute Stored Procedure...:
- A dialog will appear. Enter your chosen parameter values:
- Click OK
- SQL Server will now generate the SQL code and execute the stored procedure.
Modifying a Stored Procedure
If you need to modify an existing stored procedure, simply replace the CREATE with ALTER. Let's add a space in between "Latest" and "Tasks" (i.e. make it
"Latest Tasks") and add the Description field:
System Stored Procedures
SQL Server includes a large number of system stored procedures to assist in database administration tasks. Many of the tasks you can perform via the GUI can be done via a system stored procedure. For example, some of the things you can do with system stored procedures include:
- Configure security accounts
- Set up linked servers
- Create a database maintenance plan
- Create full text search catalogs
- Add remote login
- Configure replication
- Set up scheduled jobs
- and much more...
Expand the System Stored Procedures node and take a look. You'll notice that their names all begin with sp_ to indicate that it's a stored procedure. The system stored procedures obviously follow a naming convention.
It is a good idea to develop a consistent naming convention for your stored procedures (and for all other objects in your database).
Some people prefix their stored procedures with
usp_, others begin it with a SQL keyword such as
DELETE. Others use an abbreviation for the application.
Some use underscores to separate each word in the stored procedure (eg,
latest_tasks), while others will use title case (eg,
Therefore, it is possible that our stored procedure could been named any of the following, depening on the naming convention being used.
You get the picture. The important thing is consistency. Choose one and stick with it. It will make it easier when you need to use a stored procedure. Imagine having scores, or even hundreds of stored procedures, and every time you go to execute one, you need to navigate to it in the Object Explorer purely because you can't remember whether you called it
So that's stored procedures covered. Next we've got user logins.