store procedure for save update delete

 if you want more related search please click here  
 please help to other  
 http://scriptquery.blogspot.in/  

 USE [LMS]  
 GO  
 /****** Object: StoredProcedure [dbo].[USP_ADD_EDIT_DELETE_LEAVE_APPLICATION]  Script Date:   
 06/23/2014 11:06:11 ******/  
 SET ANSI_NULLS ON  
 GO  
 SET QUOTED_IDENTIFIER ON  
 GO  
 -- =============================================  
 -- Author     : GNANI  
 -- Create date     : 21/06/2014   
 -- Description     : Stored Procedure to ADD, EDIT AND DELETE LEAVE_APPLICATION  
 -- =============================================  
 ALTER PROCEDURE [dbo].[USP_ADD_EDIT_DELETE_LEAVE_APPLICATION]   
  @EMPID                         INT                    = NULL  
 ,@EMPCODE                    VARCHAR(MAX)     =NULL  
 ,@EMPNAME                    VARCHAR(MAX)     =NULL  
 ,@LOCATION                    VARCHAR(MAX)     =NULL  
 ,@DEPARTMENT               VARCHAR(MAX)     =NULL  
 ,@LEAVEPERIODFROM          VARCHAR(MAX)     =NULL  
 ,@LEAVEPERIODTO               VARCHAR(MAX)     =NULL  
 ,@REASONFORLEAVE          VARCHAR(MAX)     =NULL  
 ,@HRDEPTREMARKS               VARCHAR(MAX)     =NULL  
 ,@LEAVESANCNSIGN          VARCHAR(MAX)     =NULL  
 ,@LEAVENOTSANCNREMARKS     VARCHAR(MAX)     =NULL  
 ,@EARNEDLEAVE               VARCHAR(MAX)     =NULL  
 ,@OTHERLEAVE               VARCHAR(MAX)     =NULL  
 ,@LOSSOFPAY                    VARCHAR(MAX)     =NULL  
 ,@DIRECTORREMARKS          VARCHAR(MAX)     =NULL  
 ,@Type                         INT                    =0  
 ,@ResponseCode               BIGINT               OUTPUT       
 AS  
 BEGIN  
      BEGIN TRY  
           IF @Type = 1 -- ADD LEAVE_APPLICATION  
           BEGIN  
                BEGIN TRY  
                     INSERT INTO MstLeaveApplication (EMPID,          EMPCODE,       
 EMPNAME,     LOCATION,     DEPARTMENT,          LEAVEPERIODFROM,     LEAVEPERIODTO,       
 REASONFORLEAVE,          HRDEPTREMARKS,     LEAVESANCNSIGN,          LEAVENOTSANCNREMARKS,     EARNEDLEAVE,       
 OTHERLEAVE,          LOSSOFPAY,           DIRECTORREMARKS)  
                     VALUES                                   (@EMPID,       
 @EMPCODE,     @EMPNAME,     @LOCATION,     @DEPARTMENT,     @LEAVEPERIODFROM,     @LEAVEPERIODTO,       
 @REASONFORLEAVE,     @HRDEPTREMARKS,     @LEAVESANCNSIGN,     @LEAVENOTSANCNREMARKS,     @EARNEDLEAVE,       
 @OTHERLEAVE,     @LOSSOFPAY,          @DIRECTORREMARKS)  
                     SET @ResponseCode=1  
                END TRY  
                BEGIN CATCH  
                     SET @ResponseCode =-999  
                END CATCH  
           END                 
           IF @Type = 2 -- UPDATE LEAVE_APPLICATION  
           BEGIN  
                IF NOT EXISTS (SELECT 1 FROM MstLeaveApplication WITH(NOLOCK) WHERE EMPID =   
 @EMPID)   
                BEGIN  
                     SET @ResponseCode =-998  
                END  
                ELSE  
                BEGIN  
                     BEGIN TRY  
                          UPDATE     MstLeaveApplication  
                          SET          EMPNAME                    =       
 @EMPNAME  
                                 ,LOCATION               =     @LOCATION  
                                 ,DEPARTMENT               =     @DEPARTMENT  
                                 ,LEAVEPERIODFROM          =       
 @LEAVEPERIODFROM  
                                 ,LEAVEPERIODTO          =     @LEAVEPERIODTO  
                                 ,REASONFORLEAVE          =     @REASONFORLEAVE  
                                 ,HRDEPTREMARKS          =     @HRDEPTREMARKS  
                                 ,LEAVESANCNSIGN          =     @LEAVESANCNSIGN  
                                 ,LEAVENOTSANCNREMARKS=     @LEAVENOTSANCNREMARKS       
                                 ,EARNEDLEAVE               =     @EARNEDLEAVE  
                                 ,OTHERLEAVE               =     @OTHERLEAVE  
                                 ,LOSSOFPAY               =     @LOSSOFPAY  
                                 ,DIRECTORREMARKS          =       
 @DIRECTORREMARKS  
                                    WHERE EMPID               =     @EMPID  
                          SET @ResponseCode=2  
                     END TRY  
                     BEGIN CATCH  
                          SET @ResponseCode =-997  
                     END CATCH  
                END       
           END  
           IF @Type=3 -- DELETE LEAVE_APPLICATION  
           BEGIN  
                BEGIN TRY  
                     DELETE FROM MstLeaveApplication WHERE EMPID = @EMPID  
                     SET @ResponseCode=3  
                END TRY  
                BEGIN CATCH  
                     SET @ResponseCode =-996  
                END CATCH  
           END  
      END TRY  
      BEGIN CATCH  
           SELECT ERROR_MESSAGE()   
           SELECT ERROR_LINE ( )  
           SET @ResponseCode =-995  
      END CATCH  
 END  

 if you want more related search please click here  
 please help to other  
 http://scriptquery.blogspot.in/  

5:58 AM Share: