Stored Procedures (Part 1)

Stored Procedures are incredibly useful as they allow for changes to the database structure and performance without having to touch the applications themselves as the procedure can be run directly onto the database. I have been exposed to them for the past couple of months during our main project and my aim in this part is to explain what they are and what they can be used for. I want to ensure that I cover these parts fully and properly, therefore there will be a separate part for each Stored Procedure example.

What is a Stored Procedure? 

Stored Procedures are multiple T-SQL statements that combined, perform an operation when it is executed. One useful feature of stored procedures is that they can be applied to many situations and almost any command in T-SQL can be used within the procedure. Stored Procedures have numerous control flow constructs which allows for the process of the data such as:

• RETURN
• BEGIN ... END
• IF ... ELSE
• WHILE
• GOTO
• BREAK/CONTINUE
• WAITFOR

These will be discussed in more detail within a future blog post.

Stored procedures return data in a number of different ways:

• Variables (both local and global variables can be used). 
• Parameters (a form of local variable that is declared in the T-SQL itself). 
• Return codes (which are useful for error handling).
• Plus, result sets can be returned for every SELECT statement within the procedure.

Today's blog post is going to focus around Variables and Parameters as 2 methods of manipulating and storing data within your stored procedures. 

Use of Variables in Stored Procedures

A variable is a stored container that can store scalar values and have its content manipulated using programmable code. You are probably accustomed to using them in other programming languages and they are used in the same kind of way in T-SQL. There are 2 types of variable:

Global:

A Global variable is accessible in the whole of the SQL environment and can be used regardless on the database solution you are working with. They are standard variables therefore you cannot add new ones or change the contents of them, you can only read from them. It is possible to read from a global variable and store that data within a local variable that you can then manipulate. Global variables within SQL are declared with @@, see below for a small tabled list of some of the more common examples: 

Local:

A Local variable is declared by the user and only has use within that database project and that procedure or function. Unlike Global variables, you can create, read and write to local variables which makes them very useful and are probably more commonly used as well. Local variables within SQL are declared with @ and can be used with default values or not, see below for examples: 

Use of Parameters in Stored Procedures

Parameters work in a similar way to local variables, only these are passed to the stored procedure instead of declared. 

In the example below, I am setting up a simple stored procedure that attaches an ItemName to an ItemDescription and separates the 2 values with a hyphen or "-": 

As you can see, the parameters are @ItemName and @ItemDescription. These can also have default values such as "Fred" or they can be taken from the database.

When you want to execute your query, you can type the following line of code: 

If you wish to override any values, you can do so in the EXEC statement like in the example below: 

You will notice that once the procedure has been run, an error message will appear stating that the procedure has already been created. If you wish to make any changes to the procedure, you will have to change the CREATE statement to an ALTER statement like in the example below: 

To Be Continued...

Part 2 will focus on: 

• Error Messages, Return Codes and Error Handling. Stay tuned!