Overview of SQL Server Stored Procedure.

Table of Contents

1. Overview

2. What is a Stored Procedure?

2.1 System Stored Procedures

2.2 User Stored Procedures

2.3 Creating Stored Procedures

2.3.1 Performance Consideration

2.3.2 Network Consideration

3. How Stored procedures Work

4. Parameter Usage

5. Calling a Stored Procedure

6. Summary

1. Overview

When you need to store or retrieve data that is accessing and manipulating data within a database is a rather straightforward operation in SQL server database, and so we will go for T-SQL with four simple commands – SELECT, INSERT, DELETE, and UPDATE, and perform all the required tasks. The skill in working with databases lies in being able to not just work with these commands, but also complete each unit of work efficiently.

We will cover the different types of stored procedures used in SQL Server and their usage. We will also examine the working of stored procedures, the potentially tricky areas, and how to avoid them.

To increase performances, every developer should consider a number of factors during development. However, we will focus on running T-SQL from within stored procedures; we will examine what constitutes a stored procedure and discuss different types. We will cover:

• The difference between system stored procedures and user stored procedures
• Creating and designing stored procedures
• Using parameters and best practices
• Flow control statements
• Recursion
• Return values

2. What is a Stored Procedure?

If a repetitive T-SQL task has to be executed within an application, then the best repository for it is a program called a stored procedure, stored in SQL Server. Storing the code inside the SQL Server object gives us many advantages, like:

• Security due to encryption
• Performance gains due to compilation
• Being able to hold the code in a central repository:
  • Altering the code in SQL Server without replicating in several different programs
  • Being able to keep statistics on the code to keep it optimized
• Reduction in the amount of data passed over a network by keeping the code on the server
• Hiding the raw data by allowing only stored procedures to gain access to the data

You may have executed some ad-hoc queries for tasks like inserting data, querying information in other systems, or creating new database objects such as tables. All these tasks can be placed within a stored procedure, so that any developer can run the same code without having to recreate the T-SQL commands. Also, generalizing the code for all values makes it generic and reusable.

Stored procedures are more than just tools for performing repetitive tasks. There are two main types of stored procedure – system stored procedures and user-defined stored procedures. We also have extended stored procedures that can reside as either system or user-defined types. Extended stored procedures give functionality that is not necessarily contained within SQL Server, like allowing DOS commands to run and working with e-mail. It is also possible to create your own extended stored procedures.

Sample example of stored procedure:

Collapse

    /*
    DECLARE @OutPutValue VARCHAR(100)
    EXEC spExample 'CodeProject', @OutPutValue OUTPUT
    PRINT @OutPutValue
    */
    CREATE PROCEDURE [dbo].[spExample]
      @parameter1 VARCHAR(100)
     ,@parameter2 VARCHAR(200) OUTPUT
    AS
    BEGIN
    DECLARE @parameter3 VARCHAR(100)
    SET @parameter3 = ' Your development resources.'
        IF @parameter1 IS NOT NULL AND LEN(@parameter1) > 1
            SELECT @parameter2 = 'The ' 
                       + @parameter1 
                                           + @parameter3
        ELSE SELECT  @parameter2 = 'CodeProject is cool!'
    RETURN
    
    END
    GO

More details on Integration of an OLE Object with SQL Server will be found at this link.

2.1 System Stored Procedures

In SQL Server, many administrative and informational activities can be performed by using system stored procedures. Every time we add or modify a table, make a backup plan, or perform any other administrative function from within Enterprise Manager, we actually call a stored procedure specifically written to complete the desired action. These stored procedures are known as system stored procedures, which are functions that enhance the basic functionality of SQL Server itself, either by extending the functionality of an existing system stored procedure or by creating new functionality that enhances the basics of what is already there.

System stored procedures are prefixed by sp_, so it is not advisable to use sp_ for any of the stored procedures that we create, unless they form a part of our SQL Server installation. Creating a stored procedure prefixed with sp_ and placing it in the master database will make it available to any database without the need to prefix the stored procedure with the name of the database. More details can be found at this link.

Let’s clarify this with an example. If we take the sp_who stored procedure, call it sp_mywho, store it in the master database, and move to another database such as northwind, we can still execute sp_mywho, rather than having to specify the procedure in the fully qualified manner as master.dbo.sp_mywho.

2.2 User Stored Procedures

A user stored procedure is any program that is stored and compiled within SQL Server (but not in the master database) and prefixed with sp_. User stored procedures can be categorized into three distinct types:

• User stored procedures
• Triggers, and
• User defined functions

2.3 Creating Stored Procedures

The creation process depends on what we want it to do, now let’s take a look at the syntax for creating a stored procedure:

Syntax

An example of a simple stored procedure follows, where two numbers are passed in and the midpoint of the two numbers is listed:

Collapse

CREATE PROCEDURE ut_MidPoint @LowerNumber int, @HigherNumber int
AS
BEGIN

   DECLARE @Mid int
   IF @LowerNumber > @HigherNumber
      RAISERROR('You have entered your numbers the wrong way round',16,1)

   SET @Mid = ((@HigherNumber - @LowerNumber) / 2) + @LowerNumber

   SELECT @Mid

END

At the time of creation, SQL Server takes our code and parses it for any syntactical errors. Column names and variables are checked for existence at compilation. Even if they don’t exist, any temporary tables created within the stored procedure will also pass the compilation stage. This is known as deferred name resolution. It can be an asset or a drawback, as we can create temporary tables that exist for the lifetime of the stored procedure execution only, which is desirable, but if we define a temporary table and get something wrong later on, such as a column name, then the compilation will not pick up this error.

……….

Click to view the article.