Wednesday, 25 November 2015

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:
è Simple Query
Select * from K_USER_DTL
è  Stored Procedure 
    
    CREATE PROCEDURE usrdtl
      AS
              Select * from K_USER_DTL
      GO

è  Execute

      EXEC usrdtl  
è 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’
è 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.

Reff:- Read