July 30, 2014 at 1:41 am
Hi,
For resolving blocking issues, I have mention SET TRANSACTION ISOLATION LEVEL SNAPSHOT in that SP. Pls. confirm & suggest , Is it resolve the blocking issues during transaction? As below SP content
USE [OMPM]
GO
CREATE PROCEDURE [dbo].[usp_APPROVAL_SAVE]
(@TRAN_ID varchar(50),
@EMP_NO varchar(50),
@status varchar(50),
@REMARK varchar(50),
@ROLE_CODE varchar(50),
@CONTRACT_TYPE varchar(50),
@SITE varchar(50)
)
AS
BEGIN
delete from CMM_EP_APPR_DETAIL where site = @SITE
and PASS_TYPE = @CONTRACT_TYPE
and APP_TYPE = 'NEW'
and level_no in (select level_no from CMM_EP_APPR_LEVEL
where site = @SITEand PASS_TYPE = @CONTRACT_TYPE
and APP_TYPE = 'NEW'and ROLE_CODE = @ROLE_CODE)
and tran_id=@TRAN_ID
and STATUS='PENDING'
insert into CMM_EP_APPR_DETAIL (tran_id,pass_type,app_type,level_no,appr_by,appr_dt,remark,status,site)
select @TRAN_ID, PASS_TYPE, APP_TYPE, LEVEL_NO,
@EMP_NO, getdate(), @REMARK, @status, @SITE
from CMM_EP_APPR_LEVEL
where site = @SITE
and PASS_TYPE = @CONTRACT_TYPE
and APP_TYPE = 'NEW'
and ROLE_CODE = @ROLE_CODE
--remove role from cmm_new_ep_pending
if @status = 'APPROVE'
--set transaction isolation level snapshot
SET TRANSACTION ISOLATION LEVEL SNAPSHOT
begin
--insert into tmp_hari values(@TRAN_ID,@ROLE_CODE + '_1')
update cmm_new_ep_pending set pending_with=replace(pending_with,'
'+@ROLE_CODE+'
','
')
,approved_by=isnull(approved_by,'')+@ROLE_CODE+','
WHERE SITE=@SITE AND TRAN_ID=@TRAN_ID
--insert into tmp_hari values(@TRAN_ID,@ROLE_CODE)
end
--remove role from cmm_new_ep_pending
if @role_code='EIC'
SET TRANSACTION ISOLATION LEVEL SNAPSHOT
begin
/*
update CMM_EP_MASTER set eic_no=@EMP_NO,eic_name=(select empname from empmst_h where emp_no=@EMP_NO)
where site = @SITE and TRAN_ID= @TRAN_ID
*/
update CMM_EP_MASTER set eic_no=@EMP_NO,eic_name=h.empname
from EMPMST_H h
where CMM_EP_MASTER.site = @SITE
and CMM_EP_MASTER.TRAN_ID= @TRAN_ID
and h.emp_no=@EMP_NO
end
if not exists(select 1 from (
select d.STATUS
from CMM_EP_APPR_LEVEL m
left outer join CMM_EP_APPR_DETAIL d
on d.TRAN_ID = @TRAN_ID
and d.LEVEL_NO = m.LEVEL_NO
and d.PASS_TYPE = m.PASS_TYPE
and d.APP_TYPE = m.APP_TYPE
and d.SITE = m.site
AND d.STATUS != 'REJECT'
where m.site = @SITE
and m.APP_TYPE = 'NEW'
and m.ROLE_CODE != 'SECURITY'
and m.PASS_TYPE = @CONTRACT_TYPE) v
where (STATUS is null OR status='PENDING')
)
begin
update CMM_EP_MASTER
set EP_STATUS = 'APPROVE'
where site = @SITE
and TRAN_ID= @TRAN_ID
end
if @status = 'REJECT'
begin
update cmm_ep_master set ep_status='REJECT' where TRAN_ID= @TRAN_ID and site=@site
delete from cmm_new_ep_pending where TRAN_ID= @TRAN_ID and site=@site
end
if @status = 'APPROVE' and @ROLE_CODE = 'MEDICAL'
begin
update cmm_ep_master
set medical_validity = dateadd(year,1,getdate())
where TRAN_ID= @TRAN_ID
and site=@site
end
if @status = 'APPROVE' and @ROLE_CODE = 'SAFETY'
begin
update cmm_ep_master
set safety_validity = dateadd(year,1,getdate())
where TRAN_ID= @TRAN_ID
and site=@site
end
end
GO
Viewing 0 posts
You must be logged in to reply to this topic. Login to reply