Stored Procedure Practices in SQL Server

A Stored Procedure is a set of PL/SQL statements with a block of name. This is usually being used to do CRUD operations.

Road map

This article explains the following topics that can be helpful when creating and using Stored Procedures.

1. When to use Stored Procedures.
2. Basic format, indentation rules and key points for Stored Procedures.
3. Stored Procedure Template.
4. Sample table with Stored Procedure for create and update operations.
5. Advantages of Stored Procedures.
6. Drawbacks.

When to use Stored Procedures

There is no certain limitation to the use of Stored Procedures, only specific scenarios. Stored Procedures can be used to do CRUD operations. Eventhough I listed some of the scenarios, you can use a Stored Procedure instead of C# code.

1. To do a series of DML operations they are very suitable.

2. They are very suitable for a maintenance standpoint type of applications since these are very easy to do the troubleshooting.

3. Stored Procedures reduce SQL Injection attacks also. So this is also one of the options to use Stored Procedures.

4. A Stored Procedure separates the data layer from the application. So, when implementating an application, at max one or two persons can manage the entire DB operations using Stored Procedures. Others can work on code or design related stuff.

5. It helps for improved security for tables access and permissions based on user role.

6. Bulk data of insertion from one table to another with a possible set of conditions.

7. The operations that you need to do is easily fulfilled with SQL Server built-in functions or customized functions (for example, Split Custom Function, date related built-in functions and so on) when inserting or updating any data.

8. The final note is, if you have a good command over database T-SQL Statements, then creating Stored Procedures for an application is very easy for implementation and maintenance process for an application.

Basic Format, Indentation rules, Key Points for Stored Procedures

When creating a Stored Procedure, the following procedure is very useful.

1. All SQL Keywords must be in CAPITAL letters.

2. Basic Indentation (TAB space) need to be followed when creating a Stored Procedure.

   BEGIN
           IF(--CONDITION)
          BEGIN
                 --Some Statements
          END
          --Again Some Statements
   END

3. As you observed the previous sample SQL statement contains the TAB indentation after the first BEGIN statement and the same for inside the IF statement of a BEGIN condition.

4. Binding the entire Stored Procedure with a BEGIN and END block is a good practice even though it is optional.

5. The first line of a Stored Procedure block should start with the “SET NOCOUNT ON”. It helps to reduce the unnecessary network round-trips.

6. The user created Stored Procedure should not be begin with “sp_” since all system Stored Procedures are begun with “sp_” only.

7. The name of the Stored Procedure should reflect the action performed through the Stored Procedure. For example, SaveEmployee, GetEmployeeByEmployeeId and so on.

8. The Stored Procedure should contain try catch blocks and the catch block should throw the error using RAISEERROR.

9. Usually to get the existing Stored Procedure text, you can use "SP_HELPTEXT [PROCEDURE_NAME]". It returns a set of text statements for that Stored Procedure and is like  "Result to Grid" format. But, the format of text for the Stored Procedure "Results to Grid" is not suitable. When getting the Stored Procedure text using "SP_HELPTEXT [PROCEDURE_NAME]", please change that options (Results to Text (Ctrl + T)). Then, the format of Stored Procedure text remains the same and looks good.

10. Creating Stored Procedure if it contains some set of headers to identify, who has created or who has modified later and what he modified then it would help very much to the developers who will modify or understand the Stored Procedure in the future.

11. I usually follow this set of headers for the Stored Procedures:

     /*=============================================
    -- Author:Ramchand [Who has created]
    -- Create date:9 June 2015 [When its created]
    -- Description: [About the Stored Procedure]
    -- Modify By: Ramchand [Who has last modified]
    -- Last Modified Date: 1 July 2015 [When its last modified]
    -- Update: [When and what are the updates done to the stored procedure]
    --1). 1July2015- [Modified some thing by -[Developer]]
    --2). 2July2015 –[Modified another thing by -[Developer]]
    -- Unite Test: EXEC [Stored-ProcedureName] [Parameters If any]
     =============================================*/

12. Adding headers and comments when it is required (logical conditions and other operations) are highly preferred since it would be easy to understand for co-developers and you as well.

13. Use of table and temp variables are highly preferred if required and at the same time using CURSOR is not recommended in the Stored Procedure since it degrades the performance.

14. In a usual manner, the following scenarios would happen:

• You will modify some list of Stored Procedures in a day at your end. But when deploying all those to staging/production, you need to recollect all the Stored Procedures.
  
• You need to get the list of Stored Procedures for any application.
  
• You need to know when the Stored Procedure is modified.
  
  For all the previous scenarios, you can use one SQL statement, it would help. That statement is as follows.
  

  SELECT * FROM sys.procedures ORDER BY modify_date DESC

  It would return the list of Stored Procedures from the selected database with the last modified one as the first row.

15. When creating a Stored Procedure, it would be good if it contains the try catch blocks and transaction-level commits.

16. To beautify or format the Stored Procedure text very many online tools exist. It would help to store the Stored Procedure text in a correct format. Here are some of the following online tools:

• Instant SQL Formatter
• SQLFormat 

Stored Procedure Template

The following format is one of the formats I usually follow when creating Stored Procedures that include create/update/delete operations.

/*=============================================
Author:Ramchand [Who has created]
Create date:9 June 2015 [When its created]
Description: Create/Save the employee details   [About the Stored Procedure]
Modify By: [Who has last modified]
Last Modified Date: [When its last modified]
Update: [When and what are the updates done to the stored procedure]
Unit Test: EXEC [PROCEDURE_Name] [Parameters-If-any]
-- =============================================*/
CREATE PROCEDURE [PROCEDURE_NAME]
AS
BEGIN
    SET NOCOUNT ON
    BEGIN TRANSACTION
    BEGIN TRY

        --Insert/update code here
        --If everything goes fine, it commit the transaction otherwise it goes to the catch block

        --Commit the transaction at the end of stored procedure if no error occurs
        COMMIT TRANSACTION

    END TRY
    BEGIN CATCH
        --rollback the transaction as it comes to catch block
        ROLLBACK TRANSACTION

        DECLARE @ErrorNum INT = ERROR_NUMBER();
        DECLARE @ErrorLine INT = ERROR_LINE();
        DECLARE @ErrorMsg NVARCHAR(4000) = ERROR_MESSAGE();
        DECLARE @ErrorSeverity INT = ERROR_SEVERITY();
        DECLARE @ErrorState INT = ERROR_STATE();

        PRINT 'Error Number ' + CAST(@ErrorNum AS VARCHAR(10));
        PRINT 'Error Line ' + CAST(@ErrorLine AS VARCHAR(10));
        RAISERROR(@ErrorMsg, @ErrorSeverity, @ErrorState);

    END CATCH
END

Sample Table with Stored Procedure for create and update operations 

I have created one sample table named "Employee" and created a Stored Procedure "SaveEmployee". It would do the creation and update of employee details. The Stored Procedure header contains the unit test format for inserting/updating the employee details using the Stored Procedure. The following is the script for the both table and Stored Procedure. 

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
/*=============================================
Author:Ramchand [Who has created]
Create date:9 June 2015 [When its created]
Description: Create/Save the employee details   [About the Stored Procedure]
Modify By: [Who has last modified]
Last Modified Date: [When its last modified]
Update: [When and what are the updates done to the stored procedure]
Unit Test:
        For Insert: EXEC SaveEmployee 0,'Ramchand','Repalle','Male',1
        For Update : EXEC SaveEmployee 1, 'Ram','Chand','Male',1
-- =============================================*/
CREATE PROCEDURE [dbo].[SaveEmployee]
(
@EmployeeID INT,
@FirstName NVARCHAR(100),
@LastName NVARCHAR(100),
@Gender NVARCHAR(10),
@LoggedInUserID INT
)
AS
BEGIN
    SET NOCOUNT ON;
    BEGIN TRANSACTION
    BEGIN TRY
    IF(@EmployeeID > 0)
    BEGIN
        --UPDATE THE Employee, If Employee matches with the EmployeeId
        IF EXISTS(SELECT EmployeeId FROM Employee WHERE EmployeeId = @EmployeeID)
        BEGIN
            UPDATE Employee SET FirstName = @FirstName,
                                LastName = @LastName,
                                Gender = @Gender,
                                ModifiedBy = @LoggedInUserID,
                                ModifiedDate = GETDATE() WHERE EmployeeId = @EmployeeID
        END
    END
    ELSE
    BEGIN
        -- insert the employee details
        INSERT INTO Employee(FirstName,LastName,Gender,CreatedBy,CreatedDate)
                    VALUES(@FirstName,@LastName,@Gender,@LoggedInUserID,GETDATE())
        SET @EmployeeID = SCOPE_IDENTITY();

    END

    --Commit the transaction at the end of stored procedure if no error occurs
    COMMIT TRANSACTION

    END TRY
    BEGIN CATCH
        --rollback the transaction as it comes to catch block
        ROLLBACK TRANSACTION

        DECLARE @ErrorNum INT = ERROR_NUMBER();
        DECLARE @ErrorLine INT = ERROR_LINE();
        DECLARE @ErrorMsg NVARCHAR(4000) = ERROR_MESSAGE();
        DECLARE @ErrorSeverity INT = ERROR_SEVERITY();
        DECLARE @ErrorState INT = ERROR_STATE();

        PRINT 'Error Number ' + CAST(@ErrorNum AS VARCHAR(10));
        PRINT 'Error Line ' + CAST(@ErrorLine AS VARCHAR(10));
        RAISERROR(@ErrorMsg, @ErrorSeverity, @ErrorState);

    END CATCH
END
GO
/****** Object:  Table [dbo].[Employee]    Script Date: 7/2/2015 7:09:34 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Employee](
    [EmployeeId] [int] IDENTITY(1,1) NOT NULL,
    [FirstName] [nvarchar](100) NOT NULL,
    [LastName] [nvarchar](100) NOT NULL,
    [Gender] [nvarchar](10) NOT NULL,
    [CreatedBy] [int] NOT NULL,
    [CreatedDate] [datetime] NOT NULL,
    [ModifiedBy] [int] NULL,
    [ModifiedDate] [datetime] NULL,
 CONSTRAINT [PK_Employee] PRIMARY KEY CLUSTERED
(
    [EmployeeId] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

GO
ALTER TABLE [dbo].[Employee] ADD  CONSTRAINT [DF_Employee_CreatedDate]  DEFAULT (getdate()) FOR [CreatedDate]
GO

Advantages of Stored Procedures

The following are some of the advantages of using Stored Procedures.

1. Stored Procedures are complied when created and stored in executable format. So Stored Procedures calls are quick and efficient.

2. The maintenance of Stored Procedures are very easy and independent of the application since it's stored in the separate database server.

3. These can be tested individually at the database server (no dependency on application).

4. You can re-use the Stored Procedure among applications.

5. You can see the execution plan for the Stored Procedures and if required you can add indexes and other changes for performance improvement.

6. You can do the modifications and deployments to the Stored Procedure directly at the database server (independent of the application).

7. Stored Procedures provide better security in terms of SQL Injection attacks and other user-based roles and so on.

Drawbacks

Using a Stored Procedures in a correct scenario as previously explained in a relevant manner doesn't cause any problems. Eventhough the Stored Procedure are not the following types:

1. When you are going to do some operations, using loops (CURSOR) is not preferred since it causes performance effects.

2. The other scenario is that you have defined your application core logic in a Stored Procedure of SQL Server. After some period of time or some other requirement, the client has decided to migrate the database from SQL Server to Oracle, then the migration of Stored Procedures is not such a straight forward job since some of the keywords in Oracle are not the same as in SQL Server.

Conclusion

This article explained when to choose Stored Procedures, what is to be considered when creating a Stored Procedure with a template and also the definition of a sample Stored Procedure.