SQL Server 2014 - Stored Procedures

• SQL Server Views
• User Logins

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:

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.

Example

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".

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 EXECUTE or EXEC. If the stored procedure requires parameters you provide those after the procedure name. Like this:

Example

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.

Here's how:

1. Using the Object Explorer, navigate to the stored procedure
2. Right click on the stored procedure and select Execute Stored Procedure...:
   
3. A dialog will appear. Enter your chosen parameter values:
   
4. Click OK
5. 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...

Naming Conventions

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 SELECT, INSERT, UPDATE, 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, LatestTasks).

Therefore, it is possible that our stored procedure could been named any of the following, depening on the naming convention being used.

• LatestTasks
• latest_tasks
• uspLatestTasks
• usp_latest_tasks
• selectLatestTasks
• select_LatestTasks
• select_latest_tasks
• getLatestTasks
• get_latest_tasks

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 usp_LatestTasks or uspLatestTasks.

So that's stored procedures covered. Next we've got user logins.

• SQL Server Views
• User Logins