An introduction into stored procedures in MS SQL Server

Web design and development based in Royston, Hertfordshire, United Kingdom

Griffiths Web Design

An introduction into stored procedures in MS SQL Server

07709 579517

An introduction into stored procedures in MS SQL Server

By Michael Griffiths

Created: 29.07.2016 00:00:00, last updated: 29.07.2016 00:00:00

An introduction into stored procedures in MS SQL Server

Sql Server Stored Procedures

So what is a stored procedure?

A stored procedure is just a plain old database query, not unlike any other query, with one exception. A stored procedure is intended to be run over and over again. Have you ever had a query that has been used multiple times in the same project? Then that right there is an ideal candidate for a stored procedure. You can just call the sp (that’s what we will call stored procedures for the rest of this article) when and where you need it, saving yourself endless amounts of time and repetition.

In addition to sp’s being run over and over again you can also pass them parameters which enables you to create intelligent queries than can adapt to the requirements.

The advantages of stored procedures


Because scripts are in one location, updates and tracking of dependencies based on schema changes becomes easier


Can be tested independent of the application

Isolation of Business Rules

Having Stored Procedures in one location means that there’s no confusion of having business rules spread over potentially disparate code files in the application

Speed / Optimization

Stored procedures are cached on the server
Execution plans for the process are easily reviewable without having to run the application

Utilization of Set-based Processing

The power of SQL is its ability to quickly and efficiently perform set-based processing on large amounts of data; the coding equivalent is usually iterative looping, which is generally much slower


Limit direct access to tables via defined roles in the database
Provide an “interface” to the underlying data structure so that all implementation and even the data itself is shielded.
Securing just the data and the code that accesses it is easier than applying that security within the application code itself

When Should You Use Stored Procedures?

Stored Procedures may not always be the right answer for processing data, but there’s also not enough compelling evidence to not use them either. Whether or not to use them determines on your particular situation and ability to develop the Stored Procedure(s) to match. Just like with writing a good, quality application, if you or your developers can write good, quality Stored Procedures, then by all means implement them. If they can’t, then another solution might be best for you.

Stored procedures are good for common CRUD operations. For example this blog uses sp’s to add/modify/delete blog posts. These tasks are a perfect fit for sp’s because they are operations that are performed regularly and the database code remains unchanged each time. If, however, you come to a situation that requires a one off query then a stored procedure will not be a good fit.

The syntax

USE DataBaseName;
CREATE PROCEDURE [SchemaName].[ProcedureName]
@Param1Name nvarchar(50) = ‘’,
@Param2Name nvarchar(50) = ‘’

SELECT Col1Name, Col2Name
FROM [SchemaName].[tableName]
WHERE Col1Name = @Param1Name AND Col2Name = @Param2Name


As you can see the syntax is fairly straight forward and self-explanatory.

In our first line we have:

USE DataBaseName;

This line of T-Sql code tells the database server which database we wish to interact with.

Next we have the GO keyword which, as I’m sure you guessed, just tells the SQL Server utilities to send the preceding batch of statements to an instance of SQL Server. Interestingly the GO keyword is not a T-SQL statement, it is a command recognised by the osql and sqlcmd utilities.

Now we move onto:

CREATE PROCEDURE [SchemaName].[ProcedureName]

In this line we specify the schema in which our stored procedure lives and the name of the sp.

Now we have named our sp we need to give it some parameters:

@Param1Name nvarchar(50) = ‘’,
@Param2Name nvarchar(50) = ‘’

As you can see we need to specify the parameter name, which starts with a ‘@’ symbol. We also need to specify a data type, we have specified ours as nvarchar(50) but any data type is fine as long as it matches the column. We have also given our parameters a default value, which in this particular example is an empty string.

Now we have our parameters we arrive at the AS keyword. All this does is separate the declaration of the procedure from its code (the query that the sp will execute). We also have another line of code, SET NOCOUNT ON. When set nocount is on the count is not returned. This can help improve performance as the network traffic is reduced.

And now for the query that the sp will execute:

SELECT Col1Name, Col2Name
FROM [SchemaName].[tableName]
WHERE Col1Name = @Param1Name AND Col2Name = @Param2Name

As you can see this is just a simple select statement. We told it what columns to search for data that matches our parameters and we told it which table to look in for the required columns.

After our select statement we have another GO keyword, which as before, tells the Sql server utilities to execute the preceding code.

And there you have it, a brief introduction into the world of Microsoft Sql server Stored Procedures. I hope you found this article helpful and easy to read. As always I’d love some feedback (good or bad I don’t mind taking some criticism). I eagerly await your responses :) happy coding to all.

If you have a moment or two please share this article around as much as possible, that way as many people as possible can benefit from it.

About Michael Griffiths

Web Developer and Social Media Coach