SQL Server Stored Procedure

SQL Server Stored Procedure

Ø  (Introduction)

A stored procedure is nothing more than prepared SQL code that you save so you can reuse the code over and over again.  So if you think about a query that you write over and over again, instead of having to write that query each time you would save it as a stored procedure and then just call the stored procedure to execute the SQL code that you saved as part of the stored procedure.

Ø  Overview

There are various options that can be used to create stored procedures.  In these next few topics we will discuss creating a simple stored procedure to more advanced options that can be used when creating stored procedures.

Ø  Explanation

Some of the topics we will cover include:

       Creating a simple stored procedure

è Simple Query

Select * from K_USER_DTL

è  Stored Procedure 

    CREATE PROCEDURE usrdtl

      AS

              Select * from K_USER_DTL

      GO

è  Execute

      EXEC usrdtl  

       Using input parameters

è One Parameters

Select * from K_USER_DTL

è  Stored Procedure 

    CREATE PROCEDURE usrdtl @pDepartment varchar(30)

      AS

              Select * from K_USER_DTL

              Where UsrDepartment = @PDepartment

      GO

è  Execute

      EXEC usrdtl @pDepartment = ‘IT’

è Default Parameters

è  Stored Procedure 

    CREATE PROCEDURE usrdtl @pDepartment varchar(30) = NULL

      AS

              Select * from K_USER_DTL

              Where UsrDepartment = ISnull(@PDepartment, UsrDepartment)

      GO

è Multipal Parameters

è  Stored Procedure 

    CREATE PROCEDURE usrdtl  @pDepartment varchar(30) = NULL, @pCategories varchar(30) = Null

      AS

              Select * from K_USER_DTL

              Where UsrDepartment = ISnull(@PDepartment, UsrDepartment)

                       AND UsrCategories LIKE ‘%’ + isnull(@pCategories, UsrCategories) + ‘%’

      GO

è  Execute

      EXEC usrdtl @pDepartment = ‘IT’ , @pCategories = ‘ad’

       Using output parameters

è Simple Output

è  Stored Procedure 

    CREATE PROCEDURE usrdtl  @pDepartment varchar(30) = NULL, @ocount int Output

      AS

              Select @ocount  = Count(*) from K_USER_DTL

              Where UsrDepartment = ISnull(@PDepartment, UsrDepartment)

                      

      GO

è  Execute

DECLARE @totCount int

EXEC usrdtl @pDepartment = ‘IT’, @ totCount OUTPUT

SELECT @ totCount

       Using Try Catch

è  Stored Procedure 

CREATE PROCEDURE uspTryCatchTest

AS

BEGIN TRY

              SELECT 1/0

END TRY

BEGIN CATCH

     SELECT ERROR_NUMBER() AS ErrorNumber

     ,ERROR_SEVERITY() AS ErrorSeverity

     ,ERROR_STATE() AS ErrorState

     ,ERROR_PROCEDURE() AS ErrorProcedure

     ,ERROR_LINE() AS ErrorLine

     ,ERROR_MESSAGE() AS ErrorMessage;

END CATCH

è  Execute

DECLARE @totCount int

EXEC usrdtl @pDepartment = ‘IT’, @ totCount OUTPUT

SELECT @ totCount

v  Notes:

1.    Before you create a stored procedure you need to know what your end result is, whether you are selecting data, inserting data, etc..

2.    On thing to note is that you cannot use the keyword "GO" in the stored procedure.  Once the SQL Server compiler sees "GO" it assumes it is the end of the batch

3.    If you are not familiar with the Try...Catch paradigm it is basically two blocks of code with your stored procedures that lets you execute some code, this is the Try section and if there are errors they are handled in the Catch section.

4.    A great new option that was added in SQL Server 2005 was the ability to use the Try..Catch paradigm that exists in other development languages.  Doing error handling in SQL Server has not always been the easiest thing, so this option definitely makes it much easier to code for and handle errors.