SQL Stored Procedure

SQL Stored Procedures (EHD)

SQL Stored Procedures
----------------------------------------------------------------------
Here some example of SQL Stored Procedure for Insert/Update/Delete.
----------------------------------------------------------------------

USE [EHD]
GO
/****** Object:  StoredProcedure [dbo].[EHD_ENTITLEMENT_MASTER_DETAILS]    Script Date: 12/09/2017 09:49:55 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[EHD_ENTITLEMENT_MASTER_DETAILS] 
	(@flag char(1),
	@empId varchar(10))
	
AS	 
BEGIN
    Declare @SqlStr varchar(max)
    
IF(@flag='S')	-- S- Single Record. 
	BEGIN
     --set @SqlStr = 'select vTrnNo, nMonth, nCycle, vEmpId, vProjectCode, dBillRecDate, curAmount, vBillStatus, vRemarks from FIN_EMPEXPSHEET_MST where vTrnNo='''+ @trnNo +''''
     --set @SqlStr = 'select ETM.*, EmpMst.empid,EmpMst.name,EmpMst.doj,EmpMst.GrdType,EmpMst.CatType,EmpMst.BUnitName,EmpMst.SalaryBankAccNo from ENTITLEMENTDETAIL_MST as ETM
					--inner join TNS_HR.dbo.Emp_mst As EmpMst on ETM.vEmpId =' + @empId
					
	   set @SqlStr = 'Select A.*,B.name ,B.doj,B.BUnitName,B.GrdType,B.SalaryBankAccNo,B.CatType,B.ServiceCode,case when UPPER(B.emp_status)=''WORKING'' then ''A'' else ''E'' end as emp_status FROM ENTITLEMENTDETAIL_MST as A 
					  Inner Join TNS_HR.dbo.Emp_mst as B on A.vEmpId=B.empid where vEmpId='+@empId+'order by name'
     END
ELSE			--A- All Record.
	BEGIN
     set @SqlStr = 'select vTrnNo, nMonth, nCycle, vEmpId, vProjectCode, dBillRecDate, curAmount, vBillStatus, vRemarks from FIN_EMPEXPSHEET_MST where vEmpId ='+@empId
     END
     execute(@SqlStr)
    
END

--exec EHD_ENTITLEMENT_MASTER_DETAILS 'B','88822'

==========================================================================================================================

USE [EHD]
GO
/****** Object:  StoredProcedure [dbo].[EHD_EM_MASTER_INSERT]    Script Date: 12/09/2017 09:50:53 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[EHD_EM_MASTER_INSERT]
	@empId varchar(10),
	@xmlString varchar(max)
AS
BEGIN
	
	SET NOCOUNT ON;
	--select @id=max(id)+1 from EHD_EM_MASTER_INSERT

	DECLARE @docHandle INT
	 EXEC sp_xml_preparedocument @docHandle OUTPUT, @xmlString
	 
	 --Create a temp table.
	 declare @temp table(vEmpId nvarchar(30), vHotel nvarchar(50),vHotDA nvarchar(50),vDA nvarchar(50),nCommunication money, nAdvance money,nImprest money,nInternet money,nConveyance money,nProject money,vRemark nvarchar(250))
     insert into @temp(vEmpId,vHotel,vHotDA,vDA,nCommunication,nAdvance,nImprest,nInternet,nConveyance,nProject,vRemark)
     select vEmpId,vHotel,vHotDA,vDA,nCommunication,nAdvance,nImprest,nInternet,nConveyance,nProject,vRemark
     from openxml (@docHandle,'ENTITLEMENTDETAIL_MST/EMP_ROW',3)                      
     with                      
     (vEmpId nvarchar(30), vHotel nvarchar(50),vHotDA nvarchar(50),vDA nvarchar(50),nCommunication money, nAdvance money,nImprest money,nInternet money,nConveyance money,nProject money,vRemark nvarchar(250)                
      )  
     select vEmpId,vHotel,vHotDA,vDA,nCommunication,nAdvance,nImprest,nInternet,nConveyance,nProject,vRemark from @temp  
    
     insert into ENTITLEMENTDETAILLOG_MST(vEmpId,vHotel,vHotDA,vDA,nCommunication,nAdvance,nImprest,nInternet,vRemark)
     select vEmpId,vHotel,vHotDA,vDA,nCommunication,nAdvance,nImprest,nInternet,vRemark
     from ENTITLEMENTDETAIL_MST where vEmpId=@empId
     
     --delete from main table on basis of empid
     delete from ENTITLEMENTDETAIL_MST where vEmpId=@empId 
	 	
	 --insert in main table from temp table     
	 Insert into ENTITLEMENTDETAIL_MST(vEmpId,vHotel,vHotDA,vDA,nCommunication,nAdvance,nImprest,nInternet,nConveyance,nProject,vRemark)
	 SELECT vEmpId,vHotel,vHotDA,vDA,nCommunication,nAdvance,nImprest,nInternet,nConveyance,nProject,vRemark
	 FROM Openxml (@docHandle, '/ENTITLEMENTDETAIL_MST/EMP_ROW', 3)
	 WITH(vEmpId nvarchar(30), vHotel nvarchar(50),vHotDA nvarchar(50),vDA nvarchar(50),nCommunication money, nAdvance money,nImprest money,nInternet money,nConveyance money,nProject money,vRemark nvarchar(250))
	 EXEC sp_xml_removedocument @docHandle

END

=======================================================================================================================

USE [EHD]
GO
/****** Object:  StoredProcedure [dbo].[EHD_EM_MASTER_DELETE]    Script Date: 12/09/2017 09:51:47 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[EHD_EM_MASTER_DELETE]
	(@empId varchar(10))
	
AS	 

	 insert into ENTITLEMENTDETAILLOG_MST(vEmpId,vHotel,vHotDA,vDA,nCommunication,nAdvance,nImprest,nInternet,vRemark)
     select vEmpId,vHotel,vHotDA,vDA,nCommunication,nAdvance,nImprest,nInternet,vRemark
     from ENTITLEMENTDETAIL_MST where vEmpId=@empId
     
	 delete from ENTITLEMENTDETAIL_MST where vEmpId=@empId
  
    SET NOCOUNT ON;
    
=============================================================================================================================

USE [EHD]
GO
/****** Object:  StoredProcedure [dbo].[EHD_EM_MASTER_LIST]    Script Date: 12/09/2017 09:52:19 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[EHD_EM_MASTER_LIST]
	(@flag char(1),
	@empId varchar(10))
	
AS	 
BEGIN
    Declare @SqlStr varchar(max)
    
IF(@flag='S')	-- S- Single Record.
	BEGIN
     set @SqlStr = 'select vEmpId,vHotel,vHotDA,vDA,nCommunication,nAdvance,nImprest,nProject,nInternet,vRemark,nConveyance from ENTITLEMENTDETAIL_MST where vEmpId=' + @empId
     --print @SqlStr
     END
ELSE			--A- All Record.
	BEGIN
     set @SqlStr = 'select vEmpId,vHotel,vHotDA,vDA,nCommunication,nAdvance,nImprest,nProject,nInternet,vRemark,nConveyance from ENTITLEMENTDETAIL_MST'
     --set @SqlStr = 'select * from ENTITLEMENTDETAIL_MST'
     END
     execute(@SqlStr)
    
END

==============================================================================================================================
Entitlement Log Sheet (Employee Log Sheet)
----------------------------------------------------------

USE [EHD]
GO
/****** Object:  StoredProcedure [dbo].[EHD_EMP_LOG_SHEET_INSERT]    Script Date: 12/09/2017 09:53:26 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[EHD_EMP_LOG_SHEET_INSERT] 
	@logId varchar(50),
	@xmlString varchar(max)
AS
BEGIN
	
	SET NOCOUNT ON;

	DECLARE @docHandle INT
	 EXEC sp_xml_preparedocument @docHandle OUTPUT, @xmlString
	 
	 --Create a temp table.
	 declare @temp table(vTrnNo varchar(50), nMonth int , nCycle int,vEmpId varchar(15),vProjectCode varchar(15),vCircle varchar(5),vServiceCode varchar(5),vEmp_Status varchar(2), dBillRecDate datetime, curAmount money, bToACT	bit, bFromACT bit,bToPJT bit,bToACT1 bit,dBillACTDate datetime,nAnalysis int,dFromACTDate datetime,curPassingAmount	money,vVoucherNo varchar(15),vBillStatus varchar(15), vRemarks varchar(100))

     insert into @temp(vTrnNo, nMonth, nCycle,vEmpId ,vProjectCode ,vCircle ,vServiceCode ,vEmp_Status , dBillRecDate , curAmount , bToACT	, bFromACT ,bToPJT ,bToACT1 ,dBillACTDate ,nAnalysis ,dFromACTDate ,curPassingAmount,vVoucherNo ,vBillStatus , vRemarks)
     
     select vTrnNo, nMonth, nCycle,vEmpId ,vProjectCode ,vCircle ,vServiceCode ,vEmp_Status , dBillRecDate , curAmount , bToACT	, bFromACT ,bToPJT ,bToACT1 ,dBillACTDate ,nAnalysis ,dFromACTDate ,curPassingAmount,vVoucherNo ,vBillStatus , vRemarks
     from openxml (@docHandle,'FIN_EMPEXPSHEET_MST/LOG_ROW',3)                      
     with                      
     (vTrnNo varchar(50), nMonth int , nCycle int,vEmpId varchar(15),vProjectCode varchar(15),vCircle varchar(5),vServiceCode varchar(5),vEmp_Status varchar(2), dBillRecDate datetime, curAmount money, bToACT	bit, bFromACT bit,bToPJT bit,bToACT1 bit,dBillACTDate datetime,nAnalysis int,dFromACTDate datetime,curPassingAmount	money,vVoucherNo varchar(15),vBillStatus varchar(15), vRemarks varchar(100)
      )  
     select vTrnNo, nMonth, nCycle,vEmpId ,vProjectCode ,vCircle ,vServiceCode ,vEmp_Status , dBillRecDate , curAmount , bToACT	, bFromACT ,bToPJT ,bToACT1 ,dBillACTDate ,nAnalysis ,dFromACTDate ,curPassingAmount,vVoucherNo ,vBillStatus , vRemarks from @temp  
    
     insert into FIN_EMPEXPSHEETLOG_MST(vTrnNo, nMonth, nCycle,vEmpId ,vProjectCode ,vCircle ,vServiceCode ,vEmp_Status , dBillRecDate , curAmount , bToACT	, bFromACT ,bToPJT ,bToACT1 ,dBillACTDate ,nAnalysis ,dFromACTDate ,curPassingAmount,vVoucherNo ,vBillStatus)
     select vTrnNo, nMonth, nCycle,vEmpId ,vProjectCode ,vCircle ,vServiceCode ,vEmp_Status , dBillRecDate , curAmount , bToACT	, bFromACT ,bToPJT ,bToACT1 ,dBillACTDate ,nAnalysis ,dFromACTDate ,curPassingAmount,vVoucherNo ,vBillStatus
     from FIN_EMPEXPSHEET_MST where vTrnNo=@logId
     
     --delete from main table on basis of empid
     delete from FIN_EMPEXPSHEET_MST where vTrnNo=@logId 
	 	
	 --insert in main table from temp table     
	 Insert into FIN_EMPEXPSHEET_MST(vTrnNo, nMonth, nCycle,vEmpId ,vProjectCode ,vCircle ,vServiceCode ,vEmp_Status , dBillRecDate , curAmount , bToACT	, bFromACT ,bToPJT ,bToACT1 ,dBillACTDate ,nAnalysis ,dFromACTDate ,curPassingAmount,vVoucherNo ,vBillStatus , vRemarks)
	 SELECT vTrnNo, nMonth, nCycle,vEmpId ,vProjectCode ,vCircle ,vServiceCode ,vEmp_Status , dBillRecDate , curAmount , bToACT	, bFromACT ,bToPJT ,bToACT1 ,dBillACTDate ,nAnalysis ,dFromACTDate ,curPassingAmount,vVoucherNo ,vBillStatus , vRemarks
	 FROM Openxml (@docHandle,'FIN_EMPEXPSHEET_MST/LOG_ROW', 3)
	 WITH(vTrnNo varchar(50), nMonth int , nCycle int,vEmpId varchar(15),vProjectCode varchar(15),vCircle varchar(5),vServiceCode varchar(5),vEmp_Status varchar(2), dBillRecDate datetime, curAmount money, bToACT	bit, bFromACT bit,bToPJT bit,bToACT1 bit,dBillACTDate datetime,nAnalysis int,dFromACTDate datetime,curPassingAmount	money,vVoucherNo varchar(15),vBillStatus varchar(15), vRemarks varchar(100))
	 EXEC sp_xml_removedocument @docHandle

END
--select * from FIN_EMPEXPSHEET_MST

========================================================================================================================

USE [EHD]
GO
/****** Object:  StoredProcedure [dbo].[EHD_EMP_LOG_SHEET_DETAILS]    Script Date: 12/09/2017 14:37:36 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

--exec [EHD_EMP_LOG_SHEET_DETAILS] 'E','88822','0'
ALTER PROCEDURE [dbo].[EHD_EMP_LOG_SHEET_DETAILS]
	(@flag varchar (5),
	@empId varchar(10),
	@trnNo varchar(10)	
	)
	
AS	 
BEGIN
    Declare @SqlStr varchar(max)
    Declare @default varchar(1)
    
    set @default = '-'
    		
IF (@flag='E')	-- S- Single Record.
BEGIN			  
	   set @SqlStr = 'Select distinct A.*,A.vProjectCode + isnull(vCustBuyName,''' + @default + ''') as ProjectCode,A.vCircle + isnull(vAreaShort,''' + @default + ''') as Area,A.vServiceCode + isnull(vServiceDetail,''' + @default + ''') as Service,B.name as vEmpName,B.BUnitName as vBUnitName,B.GrdType as vGrade FROM FIN_EMPEXPSHEET_MST as A Inner Join TNS_HR.dbo.Emp_mst as B on A.vEmpId=B.empid left Join FIN_SERVICE_CODE As c on A.vServiceCode=c.vServiceCode left join FIN_AREA_MST D on A.vCircle=D.vAreaCode 	left join FIN_Project_Code E on A.vProjectCode=E.vCustBuyCode where vEmpId='+@empId+' order by vTrnNo'
	   print @SqlStr
END
     
ELSE
	BEGIN
       set @SqlStr = 'Select distinct A.*,A.vProjectCode + isnull(vCustBuyName,''' + @default + ''') as ProjectCode,A.vCircle + isnull(vAreaShort,''' + @default + ''') as Area,A.vServiceCode + isnull(vServiceDetail,''' + @default + ''') as Service,B.name as vEmpName,B.BUnitName as vBUnitName,B.GrdType as vGrade FROM FIN_EMPEXPSHEET_MST as A Inner Join TNS_HR.dbo.Emp_mst as B on A.vEmpId=B.empid left Join FIN_SERVICE_CODE As c on A.vServiceCode=c.vServiceCode left join FIN_AREA_MST D on A.vCircle=D.vAreaCode left join FIN_Project_Code E on A.vProjectCode=E.vCustBuyCode where vTrnNo =''' + @trnNo + ''''
       print @SqlStr
    END
    
     execute(@SqlStr)  
END

--exec EHD_EMP_LOG_SHEET_DETAILS 'E', '88822', '0'
--exec EHD_EMP_LOG_SHEET_DETAILS 'T', '0', 'J2177437'

==================================================================================================================

USE [EHD]
GO
/****** Object:  StoredProcedure [dbo].[EHD_EMP_BILL_SEARCH]    Script Date: 12/09/2017 09:55:50 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

--exec [EHD_EMP_LOG_SHEET_DETAILS] 'E','1001','0'
ALTER PROCEDURE [dbo].[EHD_EMP_BILL_SEARCH]
	(@empId varchar(10),
	@sDate varchar(15),
	@eDate varchar(15)	
	)
	
AS	 
BEGIN
    Declare @SqlStr varchar(max)		
	Declare @delimeter char(1)		

	set @delimeter = '='

BEGIN			  
	   --set @SqlStr = 'select nMonth as Month,nCycle as Cycle,vTrnNo as [Log ID],curPassingAmount as [Passing Amount],curAmount as [Bill Amount],VVoucherNo as VoucherNo,vBillStatus as BillStatus from dbo.FIN_EMPEXPSHEET_MST where vEmpId='+@empId+' and dbillRecDate between '+@sDate+' and '+@eDate+' order by nMonth ,nCycle'
	   set @SqlStr = 'select nMonth as Month,nCycle as Cycle,vTrnNo as [Log ID],curPassingAmount as [Passing Amount],curAmount as [Bill Amount],VVoucherNo as VoucherNo,vBillStatus as BillStatus from dbo.FIN_EMPEXPSHEET_MST where vEmpId='+ @empId +' and dbillRecDate between '+ 'Convert(datetime, '+@sDate+') '+ 'and '+ 'Convert(datetime, '+@eDate+')'+ ' order by nMonth ,nCycle '
	   print @SqlStr   
END

     execute(@SqlStr)  
END

exec EHD_EMP_BILL_SEARCH '88822', '2017-07-01', '2017-12-07'

==============================================================================================================================

USE [EHD]
GO
/****** Object:  StoredProcedure [dbo].[EHD_ENTITLEMENT_MASTER_DETAILS]    Script Date: 12/09/2017 09:56:37 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[EHD_ENTITLEMENT_MASTER_DETAILS] 
	(@flag char(1),
	@empId varchar(10))
	
AS	 
BEGIN
    Declare @SqlStr varchar(max)
    
IF(@flag='S')	-- S- Single Record. 
	BEGIN
     --set @SqlStr = 'select vTrnNo, nMonth, nCycle, vEmpId, vProjectCode, dBillRecDate, curAmount, vBillStatus, vRemarks from FIN_EMPEXPSHEET_MST where vTrnNo='''+ @trnNo +''''
     --set @SqlStr = 'select ETM.*, EmpMst.empid,EmpMst.name,EmpMst.doj,EmpMst.GrdType,EmpMst.CatType,EmpMst.BUnitName,EmpMst.SalaryBankAccNo from ENTITLEMENTDETAIL_MST as ETM
					--inner join TNS_HR.dbo.Emp_mst As EmpMst on ETM.vEmpId =' + @empId
					
	   set @SqlStr = 'Select A.*,B.name ,B.doj,B.BUnitName,B.GrdType,B.SalaryBankAccNo,B.CatType,B.ServiceCode,case when UPPER(B.emp_status)=''WORKING'' then ''A'' else ''E'' end as emp_status FROM ENTITLEMENTDETAIL_MST as A 
					  Inner Join TNS_HR.dbo.Emp_mst as B on A.vEmpId=B.empid where vEmpId='+@empId+'order by name'
     END
ELSE			--A- All Record.
	BEGIN
     set @SqlStr = 'select vTrnNo, nMonth, nCycle, vEmpId, vProjectCode, dBillRecDate, curAmount, vBillStatus, vRemarks from FIN_EMPEXPSHEET_MST where vEmpId ='+@empId
     END
     execute(@SqlStr)
    
END

exec EHD_ENTITLEMENT_MASTER_DETAILS 'B','88822'

===============================================================================================================

USE [EHD]
GO
/****** Object:  StoredProcedure [dbo].[EHD_GET_EMP_STATUS]    Script Date: 12/09/2017 09:57:04 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[EHD_GET_EMP_STATUS]
	(@flag char(15),
	@empId varchar(10))
	
AS	 
BEGIN
    Declare @SqlStr varchar(max)
    
IF(@flag='EMPSTATUS')	
	BEGIN
     --set @SqlStr = 'Select * from TNS_HR.dbo.hr_fullnfinalmaster A inner join TNS_HR.dbo.Emp_mst B on B.empid=A.empid where A.empid='+@empId
     set @SqlStr ='Select emp_status FROM TNS_HR.dbo.Emp_mst where empid='+@empId
     END
ELSE		
	BEGIN
     --set @SqlStr = 'Select * from TNS_HR.dbo.hr_fullnfinalmaster A inner join TNS_HR.dbo.Emp_mst B on B.empid=A.empid where A.empid='+@empId
     set @SqlStr ='Select emp_status FROM TNS_HR.dbo.Emp_mst where empid='+@empId
     END
     execute(@SqlStr)
    
END

exec EHD_GET_EMP_LOGSHEET_DETAILS 'EMPSTATUS','13104'