please solve this deadlock problem

i have 2 stored procedures
1st sp
CREATE procedure dbo.sfd_sp_Assign_PR
@.VOICE_FILE_LOCATION VARCHAR(255),
@.VOICE_FILE_NAME VARCHAR(255),
@.PR VARCHAR(10),
@.TYPE CHAR(1),
@.ACCOUNTID varchar(10) OUTPUT,
@.DICTATOR VARCHAR(50) OUTPUT
AS
DECLARE @.Count as int
DECLARE @.LAST_PR as VARCHAR(10)
DECLARE @.TR as VARCHAR(10)
DECLARE @.PROVIDER_ID as smallint
DECLARE @.DURATION as int
DECLARE @.SHIFT_ID as varchar(10)
DECLARE @.TR_SHIFT_ID as varchar(10)
DECLARE @.FILE_ASSIGNED_DATE as datetime
DECLARE @.TR_ASSIGNED_TIME as datetime
DECLARE @.undertranscription as int
DECLARE @.readyforproofreading as int
DECLARE @.MaxSequence as int
DECLARE @.WEIGHTED_PR_DURATION as int
DECLARE @.PR_VOICE_WEIGHT as int
DECLARE @.TR_STATUS AS CHAR(1)
DECLARE @.PR_DOC_FILE_NAME AS VARCHAR(255)
DECLARE @.PR_DOC_FILE_LOCATION AS VARCHAR(255)
DECLARE @.PATIENT_NAME AS VARCHAR(50)
DECLARE @.MRN AS VARCHAR(20)
DECLARE @.VISIT_DATE AS SMALLDATETIME
DECLARE @.TR_CHAR_COUNT AS INT
DECLARE @.VF_START_TIME AS SMALLINT
DECLARE @.VF_END_TIME AS SMALLINT
DECLARE @.TR_NOTES AS VARCHAR(1000)
DECLARE @.TR_CHECKIN_DATETIME AS DATETIME
DECLARE @.REPORT_TYPE AS VARCHAR(50)
DECLARE @.TR_PL_STATUS AS SMALLINT
DECLARE @.LOCATIONID AS SMALLINT
DECLARE @.TR_TRANSCRIPTS CURSOR
SELECT @.ACCOUNTID=ACCOUNTID,@.PROVIDER_ID=PROVID
ER_ID,@.DURATION=DURATION,
@.WEIGHTED_PR_DURATION=WEIGHTED_PR_DURATI
ON FROM VOICE_FILES with (nolock)
WHERE
VOICE_FILE_LOCATION=@.VOICE_FILE_LOCATION
AND VOICE_FILE_NAME=@.VOICE_FILE_NAME
IF @.ACCOUNTID is NULL
return -1 --No record in voice files
SELECT @.Count=COUNT(*) FROM ACCOUNT_WEIGHTS with (nolock) WHERE
ACCOUNTID=@.ACCOUNTID AND PROVIDER_ID=@.PROVIDER_ID
If @.Count = 0
return -2 --No record in account weights
SELECT @.Count=count(*) from DISTRIBUTION with (nolock) where
VOICE_FILE_LOCATION=@.VOICE_FILE_LOCATION
AND VOICE_FILE_NAME=@.VOICE_FILE_NAME
If @.Count = 0
return -3 --File not assigned to TR for transcription
SELECT @.Count=count(*) FROM VOICE_FILE_PRIORITY with (nolock) WHERE
VOICE_FILE_NAME=@.VOICE_FILE_NAME AND
VOICE_FILE_LOCATION=@.VOICE_FILE_LOCATION
If @.Count = 0
return -4 --No record in voice file priority
SELECT assigned_to_pr FROM DISTRIBUTION with (nolock) WHERE
VOICE_FILE_LOCATION=@.VOICE_FILE_LOCATION
AND VOICE_FILE_NAME=@.VOICE_FILE_NAME and ASSIGNED_TO_PR=@.PR
if @.@.rowcount=1
return -5 --file already assigned to selected PR
SELECT @.SHIFT_ID=SHIFT_ID FROM EMP_SHIFT with (nolock) WHERE EMPID=@.PR AND
getdate() >= EFFECTIVE_DATE AND DAY_OF_WEEK = DATEPART(dw,getdate())
If @.SHIFT_ID IS NULL
return -6 --Shift ID not found
SET @.undertranscription = 0
SET @.readyforproofreading = 0
SELECT @.MaxSequence=max(sequence) from distribution with (nolock) WHERE
VOICE_FILE_LOCATION=@.VOICE_FILE_LOCATION
AND VOICE_FILE_NAME=@.VOICE_FILE_NAME
SELECT @.LAST_PR=ASSIGNED_TO_PR FROM DISTRIBUTION with (nolock) WHERE
VOICE_FILE_LOCATION=@.VOICE_FILE_LOCATION
AND VOICE_FILE_NAME=@.VOICE_FILE_NAME and sequence=@.MaxSequence
IF @.MaxSequence=1 AND @.LAST_PR IS NULL
BEGIN
SELECT @.Count=COUNT(*) FROM VF_TR_ASSIGN with (nolock) WHERE STATUS in
('A','O') AND VOICE_FILE_LOCATION=@.VOICE_FILE_LOCATION
AND VOICE_FILE_NAME=@.VOICE_FILE_NAME
IF @.Count > 0
SET @.undertranscription = @.Duration
SELECT @.Count=COUNT(*) FROM VF_TR_ASSIGN with (nolock) WHERE STATUS='C' AND
VOICE_FILE_LOCATION=@.VOICE_FILE_LOCATION
AND VOICE_FILE_NAME=@.VOICE_FILE_NAME
IF @.Count > 0
SET @.readyforproofreading = @.Duration
END
SELECT @.PR_VOICE_WEIGHT=PR_VOICE_WEIGHT FROM ACCOUNT_WEIGHTS_EXCP with
(nolock) WHERE ACCOUNTID=@.ACCOUNTID
AND PROVIDER_ID=@.PROVIDER_ID AND EMPID=@.PR
if @.PR_VOICE_WEIGHT IS NOT NULL
SET @.WEIGHTED_PR_DURATION = @.PR_VOICE_WEIGHT * @.DURATION
--SELECT @.DICTATOR=FIRST_NAME + ' ' + LAST_NAME FROM PROVIDER WHERE
PROVIDER_ID=@.PROVIDER_ID
SELECT @.DICTATOR=DICTATED_BY FROM PROVIDER WHERE PROVIDER_ID=@.PROVIDER_ID
SELECT @.FILE_ASSIGNED_DATE=dbo. sfd_fn_File_Assigned_Date(@.PR,getdate())
SET @.FILE_ASSIGNED_DATE=CONVERT(VARCHAR(12),
@.FILE_ASSIGNED_DATE,101)
BEGIN TRANSACTION
IF @.LAST_PR IS NULL AND @.MaxSequence=1
BEGIN
UPDATE TR_TRANSCRIPTS with (updlock) SET ASSIGNED_TO_PR=@.PR WHERE
VOICE_FILE_LOCATION=@.VOICE_FILE_LOCATION
AND VOICE_FILE_NAME=@.VOICE_FILE_NAME
If @.@.ERROR !=0
BEGIN
ROLLBACK TRANSACTION
RETURN 0
END
UPDATE DISTRIBUTION with (updlock) SET
ASSIGNED_TO_PR=@.PR,PR_SHIFT_ID=@.SHIFT_ID
,
PR_ASSIGNED_TIME=GETDATE(),DISTRIBUTION_
TYPE=@.TYPE WHERE
VOICE_FILE_LOCATION=@.VOICE_FILE_LOCATION
AND VOICE_FILE_NAME=@.VOICE_FILE_NAME AND SEQUENCE=1
If @.@.ERROR !=0
BEGIN
ROLLBACK TRANSACTION
RETURN 0
END
UPDATE VOICE_FILE_PRIORITY with (updlock) SET ASSIGNED_TO_PR=@.PR WHERE
VOICE_FILE_LOCATION=@.VOICE_FILE_LOCATION
AND VOICE_FILE_NAME=@.VOICE_FILE_NAME
If @.@.ERROR != 0
BEGIN
ROLLBACK TRANSACTION
RETURN 0
END
END
ELSE
BEGIN
SELECT @.TR_STATUS=TR_STATUS, @.TR=ASSIGNED_TO_TR, @.TR_SHIFT_ID=TR_SHIFT_ID,
@.TR_ASSIGNED_TIME=TR_ASSIGNED_TIME
FROM DISTRIBUTION with (nolock) WHERE sequence=1 AND
VOICE_FILE_LOCATION=@.VOICE_FILE_LOCATION
AND VOICE_FILE_NAME=@.VOICE_FILE_NAME
INSERT INTO DISTRIBUTION (VOICE_FILE_LOCATION, VOICE_FILE_NAME,
TR_STATUS,SEQUENCE,NOTES,PR_STATUS,ASSIG
NED_TO_TR,ASSIGNED_TO_PR,TR_SHIFT_ID
,PR_SHIFT_ID,TR_ASSIGNED_TIME,PR_ASSIGNE
D_TIME,DISTRIBUTION_TYPE) values
(@.VOICE_FILE_LOCATION, @.VOICE_FILE_NAME, @.TR_STATUS, @.MaxSequence+1,
'Distributed to Tr', 'N', @.TR, @.PR, @.TR_SHIFT_ID, @.SHIFT_ID,
@.TR_ASSIGNED_TIME, GETDATE(),@.TYPE)
-- SELECT @.COUNT=COUNT(*) FROM TR_TRANSCRIPTS WHERE PR_STATUS IN ('N','P')
AND VOICE_FILE_LOCATION=@.VOICE_FILE_LOCATION
-- AND VOICE_FILE_NAME=@.VOICE_FILE_NAME
-- IF @.COUNT = 0
-- BEGIN
-- SELECT @.COUNT=COUNT(*) FROM TR_TRANSCRIPTS WHERE
VOICE_FILE_LOCATION=@.VOICE_FILE_LOCATION
-- AND VOICE_FILE_NAME=@.VOICE_FILE_NAME
-- IF @.COUNT > 0
-- BEGIN
UPDATE VOICE_FILE_PRIORITY with (updlock) SET ASSIGNED_TO_PR=@.PR WHERE
VOICE_FILE_LOCATION=@.VOICE_FILE_LOCATION
AND VOICE_FILE_NAME=@.VOICE_FILE_NAME AND ASSIGNED_TO_PR IS NULL
If @.@.ERROR != 0
BEGIN
ROLLBACK TRANSACTION
RETURN 0
END
-- END
-- END
SET @.TR_TRANSCRIPTS = CURSOR FOR
SELECT
PATIENT_NAME,MRN,VISIT_DATE,TR_CHAR_COUN
T,PR_DOC_FILE_NAME,PR_DOC_FILE_LOCAT
ION,VF_START_TIME,VF_END_TIME,TR_NOTES,R
EPORT_TYPE,TR_CHECKIN_DATETIME,TR_PL
_STATUS,ASSIGNED_TO_TR,LOCATIONID
FROM TR_TRANSCRIPTS with (nolock) where ASSIGNED_TO_PR=@.LAST_PR AND
VOICE_FILE_LOCATION=@.VOICE_FILE_LOCATION
AND VOICE_FILE_NAME=@.VOICE_FILE_NAME AND PR_DOC_FILE_NAME IS NOT NULL
OPEN @.TR_TRANSCRIPTS
FETCH NEXT FROM @.TR_TRANSCRIPTS INTO
@.PATIENT_NAME,@.MRN,@.VISIT_DATE,@.TR_CHAR_
COUNT,@.PR_DOC_FILE_NAME,@.PR_DOC_FILE
_LOCATION,@.VF_START_TIME,@.VF_END_TIME,@.T
R_NOTES,@.REPORT_TYPE,@.TR_CHECKIN_DAT
ETIME,@.TR_PL_STATUS,@.TR,@.LOCATIONID
WHILE (@.@.FETCH_STATUS = 0)
BEGIN
INSERT INTO
TR_TRANSCRIPTS(DOC_FILE_NAME,DOC_FILE_LO
CATION,PATIENT_NAME,MRN,VISIT_DATE,T
RANSCRIPTION_STATUS,TR_CHAR_COUNT,VOICE_
FILE_NAME,VOICE_FILE_LOCATION,ASSIGN
ED_TO_TR,TR_CHECKIN_DATETIME,PR_STATUS,V
F_START_TIME,VF_END_TIME,TR_NOTES,RE
PORT_TYPE,TR_PL_STATUS,ASSI
GNED_TO_PR,LOCATIONID) VALUES
(@.PR_DOC_FILE_NAME,@.PR_DOC_FILE_LOCATION
,@.PATIENT_NAME,@.MRN,@.VISIT_DATE,'A',
@.TR_CHAR_COUNT,@.VOICE_FILE_NAME,@.VOICE_F
ILE_LOCATION,@.TR,@.TR_CHECKIN_DATETIM
E,'N',@.VF_START_TIME,@.VF_END_TIME,@.TR_NO
TES,@.REPORT_TYPE,@.TR_PL_STATUS,@.PR,@.
LOCATIONID)
SET @.undertranscription = 0
SET @.readyforproofreading = @.Duration
FETCH NEXT FROM @.TR_TRANSCRIPTS INTO
@.PATIENT_NAME,@.MRN,@.VISIT_DATE,@.TR_CHAR_
COUNT,@.PR_DOC_FILE_NAME,@.PR_DOC_FILE
_LOCATION,@.VF_START_TIME,@.VF_END_TIME,@.T
R_NOTES,@.REPORT_TYPE,@.TR_CHECKIN_DAT
ETIME,@.TR_PL_STATUS,@.TR,@.LOCATIONID
END
CLOSE @.TR_TRANSCRIPTS
DEALLOCATE @.TR_TRANSCRIPTS
--UPDATE EMP_ASSIGNED_WORKLOAD
END
--common
UPDATE VOICE_FILES with (updlock) SET STATUS='A',TYPE= CASE WHEN TYPE='B'
OR TYPE='R' THEN 'N' ELSE TYPE END WHERE
VOICE_FILE_LOCATION=@.VOICE_FILE_LOCATION
AND
VOICE_FILE_NAME=@.VOICE_FILE_NAME
If @.@.ERROR !=0
BEGIN
ROLLBACK TRANSACTION
RETURN 0
END
IF EXISTS(SELECT empid from emp_assigned_workload with (nolock) WHERE
EMPID=@.PR AND FILE_ASSIGNED_DATE=@.FILE_ASSIGNED_DATE
AND SHIFT_ID=@.SHIFT_ID)
UPDATE EMP_ASSIGNED_WORKLOAD with (updlock) SET
UNDER_TRANSCRIPTION=UNDER_TRANSCRIPTION+
@.undertranscription,READY_FOR_PROOFR
EADING=READY_FOR_PROOFREADING+@.readyforp
roofreading,
WORK_ASSIGNED=WORK_ASSIGNED+@.WEIGHTED_PR
_DURATION,
PR_WORK_ASSIGNED=PR_WORK_ASSIGNED+@.DURAT
ION
WHERE EMPID=@.PR AND FILE_ASSIGNED_DATE=@.FILE_ASSIGNED_DATE AND
SHIFT_ID=@.SHIFT_ID
ELSE
INSERT INTO
EMP_ASSIGNED_WORKLOAD(FILE_ASSIGNED_DATE
,EMPID,PR_WORK_COMPLETED,TR_WORK_COM
PLETED,
UNDER_TRANSCRIPTION,READY_FOR_PROOFREADI
NG,PR_WORK_ASSIGNED,TR_WORK_ASSIGNED
,SHIFT_ID,
WORK_ASSIGNED) VALUES (@.FILE_ASSIGNED_DATE,@.PR,0,0,@.undertrans
cription,
@.readyforproofreading,@.DURATION,0,@.SHIFT
_ID,@.WEIGHTED_PR_DURATION)
If @.@.ERROR !=0 OR @.@.ROWCOUNT=0
BEGIN
ROLLBACK TRANSACTION
RETURN -10
END
COMMIT TRANSACTION
RETURN 1
GO
--end 1st sp
2nd sp
CREATE procedure dbo.sfd_sp_Assign_TR
@.VOICE_FILE_LOCATION VARCHAR(255),
@.VOICE_FILE_NAME VARCHAR(255),
@.TR VARCHAR(10),
@.ACCOUNTID varchar(10) output,
@.DICTATOR VARCHAR(50) output
AS
DECLARE @.Count as int
DECLARE @.PROVIDER_ID as smallint
DECLARE @.WEIGHTED_TR_DURATION as int
DECLARE @.DURATION as int
DECLARE @.SHIFT_ID as varchar(10)
DECLARE @.STAT_FLAG as char(1)
DECLARE @.MAX_Priority as int
DECLARE @.PRIORITY as bit
DECLARE @.FILE_ASSIGNED_DATE as datetime
DECLARE @.TRANS_VOICE_WEIGHT AS int
DECLARE @.MASTER_BLOCKED AS int
SELECT @.ACCOUNTID=ACCOUNTID,@.PROVIDER_ID=PROVID
ER_ID,@.DURATION=DURATION,
@.WEIGHTED_TR_DURATION=WEIGHTED_TR_DURATI
ON,@.STAT_FLAG=STAT_FLAG FROM
VOICE_FILES with (nolock) WHERE
VOICE_FILE_LOCATION=@.VOICE_FILE_LOCATION
AND VOICE_FILE_NAME=@.VOICE_FILE_NAME
IF @.ACCOUNTID is NULL
return -1 --No record in voice files
SELECT @.Count=COUNT(*) FROM ACCOUNT_WEIGHTS WHERE ACCOUNTID=@.ACCOUNTID AND
PROVIDER_ID=@.PROVIDER_ID
If @.Count = 0
return -2 --No record in account weights
SELECT @.Count=count(*) from vf_tr_assign where
VOICE_FILE_LOCATION=@.VOICE_FILE_LOCATION
AND VOICE_FILE_NAME=@.VOICE_FILE_NAME
If @.Count > 0
return -3 --TR has already started working on the file
SELECT @.SHIFT_ID=SHIFT_ID FROM EMP_SHIFT with (nolock) WHERE EMPID=@.TR AND
getdate() >= EFFECTIVE_DATE AND DAY_OF_WEEK = datepart(dw,getdate())
If @.SHIFT_ID is null
return -4 --Shift ID not found
SELECT @.Count=count(*) from DISTRIBUTION where
VOICE_FILE_LOCATION=@.VOICE_FILE_LOCATION
AND VOICE_FILE_NAME=@.VOICE_FILE_NAME
If @.Count > 0
return -5 --File already assigned for transcription
if @.STAT_FLAG='S' or @.STAT_FLAG='P'
SELECT @.MAX_Priority=MAX(PRIORITY) FROM VOICE_FILE_PRIORITY with (nolock)
WHERE IMPORTANCE=@.STAT_FLAG
ELSE
BEGIN
SELECT @.PRIORITY=PRIORITY FROM ACCOUNT with (nolock) WHERE
ACCOUNTID=@.ACCOUNTID
IF @.PRIORITY is null
set @.PRIORITY=0
IF @.PRIORITY = 1
BEGIN
SELECT @.MAX_Priority=MAX(PRIORITY) FROM VOICE_FILE_PRIORITY with (nolock)
WHERE IMPORTANCE='N'
set @.STAT_FLAG='N'
END
ELSE
BEGIN
SELECT @.MAX_Priority=MAX(PRIORITY) FROM VOICE_FILE_PRIORITY with (nolock)
WHERE IMPORTANCE='L'
set @.STAT_FLAG='L'
END
END
if @.MAX_Priority is null
set @.MAX_Priority = 0
set @.MAX_Priority = @.MAX_Priority + 1
SELECT @.TRANS_VOICE_WEIGHT=TRANS_VOICE_WEIGHT FROM ACCOUNT_WEIGHTS_EXCP with
(nolock) WHERE ACCOUNTID=@.ACCOUNTID
AND PROVIDER_ID=@.PROVIDER_ID AND EMPID=@.TR
if @.TRANS_VOICE_WEIGHT is not null
SET @.WEIGHTED_TR_DURATION = @.TRANS_VOICE_WEIGHT * @.DURATION
--SELECT @.DICTATOR=FIRST_NAME + ' ' + LAST_NAME FROM PROVIDER WHERE
PROVIDER_ID=@.PROVIDER_ID
SELECT @.DICTATOR=DICTATED_BY FROM PROVIDER with (nolock) WHERE
PROVIDER_ID=@.PROVIDER_ID
SELECT @.FILE_ASSIGNED_DATE = dbo. sfd_fn_File_Assigned_Date(@.TR,getdate())
SET @.FILE_ASSIGNED_DATE = CONVERT(VARCHAR(12),@.FILE_ASSIGNED_DATE,
101)
BEGIN transaction
Insert into
dbo. Distribution(VOICE_FILE_LOCATION,VOICE_F
ILE_NAME,ASSIGNED_TO_TR,TR_SHIFT
_ID,NOTES,
TR_ASSIGNED_TIME) values (@.VOICE_FILE_LOCATION,@.VOICE_FILE_NAME,@.
TR,@.SHIFT_I
D
,'Distributed to Tr',GETDATE())
If @.@.error != 0
BEGIN
rollback transaction
RETURN 0
END
IF EXISTS(SELECT EMPID from emp_assigned_workload WHERE EMPID=@.TR AND
FILE_ASSIGNED_DATE=@.FILE_ASSIGNED_DATE
AND SHIFT_ID=@.SHIFT_ID)
Update EMP_ASSIGNED_WORKLOAD with (updlock) SET
WORK_ASSIGNED=WORK_ASSIGNED+@.WEIGHTED_TR
_DURATION,
TR_WORK_ASSIGNED=TR_WORK_ASSIGNED+@.DURAT
ION WHERE EMPID=@.TR AND
FILE_ASSIGNED_DATE=@.FILE_ASSIGNED_DATE
AND SHIFT_ID=@.SHIFT_ID
ELSE
INSERT INTO
EMP_ASSIGNED_WORKLOAD(FILE_ASSIGNED_DATE
,EMPID,PR_WORK_COMPLETED,TR_WORK_COM
PLETED,
UNDER_TRANSCRIPTION,READY_FOR_PROOFREADI
NG,PR_WORK_ASSIGNED,TR_WORK_ASSIGNED
,SHIFT_ID,
WORK_ASSIGNED) VALUES (@.FILE_ASSIGNED_DATE,@.TR,0,0,0,0,0,@.DURA
TION,@.SHIFT_ID
,
@.WEIGHTED_TR_DURATION)
If @.@.error != 0 OR @.@.ROWCOUNT=0
BEGIN
rollback transaction
RETURN -10
END
INSERT INTO
VOICE_FILE_PRIORITY(VOICE_FILE_LOCATION,
VOICE_FILE_NAME,ACCOUNT_ID,PROVIDER_
ID,
PRIORITY,ASSIGNED_TO_TR,ASSIGNED_TO_PR,I
MPORTANCE,STATUS,PENDINGSTATUS)
VALUES
(@.VOICE_FILE_LOCATION,@.VOICE_FILE_NAME,@.
ACCOUNTID,@.PROVIDER_ID,
@.MAX_Priority,@.TR ,NULL,@.STAT_FLAG,'N',NULL)
If @.@.error != 0
BEGIN
rollback transaction
RETURN 0
END
-- written by raghu for deadlock
select @.MASTER_BLOCKED = blocked from dbo.master.sysprocesses with (nolock)
where blocked<>0
if @.MASTER_BLOCKED=0
begin
UPDATE VOICE_FILES with (updlock) SET STATUS='T',TYPE= CASE WHEN TYPE='B'
OR TYPE='R' THEN 'N' ELSE TYPE END,UPDATE_TIME=getdate()
WHERE VOICE_FILE_LOCATION=@.VOICE_FILE_LOCATION
AND
VOICE_FILE_NAME=@.VOICE_FILE_NAME
If @.@.error <> 0 or @.@.rowcount=0
BEGIN
rollback transaction
RETURN 0
END
end
else
begin
SET LOCK_TIMEOUT 30
if @.@.LOCK_TIMEOUT= 30
rollback transaction
RETURN 0
end
-- end by raghu
COMMIT TRANSACTION
RETURN 1
GO
--end spHi
Please read this article
http://www.sql-server-performance.com/deadlocks.asp
"raghu veer" <raghu veer@.discussions.microsoft.com> wrote in message
news:E05516F6-C1D4-418F-9111-C7EFD74B5BF3@.microsoft.com...
>i have 2 stored procedures
> 1st sp
> CREATE procedure dbo.sfd_sp_Assign_PR
> @.VOICE_FILE_LOCATION VARCHAR(255),
> @.VOICE_FILE_NAME VARCHAR(255),
> @.PR VARCHAR(10),
> @.TYPE CHAR(1),
> @.ACCOUNTID varchar(10) OUTPUT,
> @.DICTATOR VARCHAR(50) OUTPUT
> AS
> DECLARE @.Count as int
> DECLARE @.LAST_PR as VARCHAR(10)
> DECLARE @.TR as VARCHAR(10)
> DECLARE @.PROVIDER_ID as smallint
> DECLARE @.DURATION as int
> DECLARE @.SHIFT_ID as varchar(10)
> DECLARE @.TR_SHIFT_ID as varchar(10)
> DECLARE @.FILE_ASSIGNED_DATE as datetime
> DECLARE @.TR_ASSIGNED_TIME as datetime
> DECLARE @.undertranscription as int
> DECLARE @.readyforproofreading as int
> DECLARE @.MaxSequence as int
> DECLARE @.WEIGHTED_PR_DURATION as int
> DECLARE @.PR_VOICE_WEIGHT as int
> DECLARE @.TR_STATUS AS CHAR(1)
> DECLARE @.PR_DOC_FILE_NAME AS VARCHAR(255)
> DECLARE @.PR_DOC_FILE_LOCATION AS VARCHAR(255)
> DECLARE @.PATIENT_NAME AS VARCHAR(50)
> DECLARE @.MRN AS VARCHAR(20)
> DECLARE @.VISIT_DATE AS SMALLDATETIME
> DECLARE @.TR_CHAR_COUNT AS INT
> DECLARE @.VF_START_TIME AS SMALLINT
> DECLARE @.VF_END_TIME AS SMALLINT
> DECLARE @.TR_NOTES AS VARCHAR(1000)
> DECLARE @.TR_CHECKIN_DATETIME AS DATETIME
> DECLARE @.REPORT_TYPE AS VARCHAR(50)
> DECLARE @.TR_PL_STATUS AS SMALLINT
> DECLARE @.LOCATIONID AS SMALLINT
> DECLARE @.TR_TRANSCRIPTS CURSOR
> SELECT @.ACCOUNTID=ACCOUNTID,@.PROVIDER_ID=PROVID
ER_ID,@.DURATION=DURATION,
> @.WEIGHTED_PR_DURATION=WEIGHTED_PR_DURATI
ON FROM VOICE_FILES with (nolock)
> WHERE
> VOICE_FILE_LOCATION=@.VOICE_FILE_LOCATION
> AND VOICE_FILE_NAME=@.VOICE_FILE_NAME
> IF @.ACCOUNTID is NULL
> return -1 --No record in voice files
> SELECT @.Count=COUNT(*) FROM ACCOUNT_WEIGHTS with (nolock) WHERE
> ACCOUNTID=@.ACCOUNTID AND PROVIDER_ID=@.PROVIDER_ID
> If @.Count = 0
> return -2 --No record in account weights
> SELECT @.Count=count(*) from DISTRIBUTION with (nolock) where
> VOICE_FILE_LOCATION=@.VOICE_FILE_LOCATION
> AND VOICE_FILE_NAME=@.VOICE_FILE_NAME
> If @.Count = 0
> return -3 --File not assigned to TR for transcription
> SELECT @.Count=count(*) FROM VOICE_FILE_PRIORITY with (nolock) WHERE
> VOICE_FILE_NAME=@.VOICE_FILE_NAME AND
> VOICE_FILE_LOCATION=@.VOICE_FILE_LOCATION
> If @.Count = 0
> return -4 --No record in voice file priority
> SELECT assigned_to_pr FROM DISTRIBUTION with (nolock) WHERE
> VOICE_FILE_LOCATION=@.VOICE_FILE_LOCATION
> AND VOICE_FILE_NAME=@.VOICE_FILE_NAME and ASSIGNED_TO_PR=@.PR
> if @.@.rowcount=1
> return -5 --file already assigned to selected PR
> SELECT @.SHIFT_ID=SHIFT_ID FROM EMP_SHIFT with (nolock) WHERE EMPID=@.PR AND
> getdate() >= EFFECTIVE_DATE AND DAY_OF_WEEK = DATEPART(dw,getdate())
> If @.SHIFT_ID IS NULL
> return -6 --Shift ID not found
> SET @.undertranscription = 0
> SET @.readyforproofreading = 0
> SELECT @.MaxSequence=max(sequence) from distribution with (nolock) WHERE
> VOICE_FILE_LOCATION=@.VOICE_FILE_LOCATION
> AND VOICE_FILE_NAME=@.VOICE_FILE_NAME
> SELECT @.LAST_PR=ASSIGNED_TO_PR FROM DISTRIBUTION with (nolock) WHERE
> VOICE_FILE_LOCATION=@.VOICE_FILE_LOCATION
> AND VOICE_FILE_NAME=@.VOICE_FILE_NAME and sequence=@.MaxSequence
> IF @.MaxSequence=1 AND @.LAST_PR IS NULL
> BEGIN
> SELECT @.Count=COUNT(*) FROM VF_TR_ASSIGN with (nolock) WHERE STATUS in
> ('A','O') AND VOICE_FILE_LOCATION=@.VOICE_FILE_LOCATION
> AND VOICE_FILE_NAME=@.VOICE_FILE_NAME
> IF @.Count > 0
> SET @.undertranscription = @.Duration
> SELECT @.Count=COUNT(*) FROM VF_TR_ASSIGN with (nolock) WHERE STATUS='C'
> AND
> VOICE_FILE_LOCATION=@.VOICE_FILE_LOCATION
> AND VOICE_FILE_NAME=@.VOICE_FILE_NAME
> IF @.Count > 0
> SET @.readyforproofreading = @.Duration
> END
> SELECT @.PR_VOICE_WEIGHT=PR_VOICE_WEIGHT FROM ACCOUNT_WEIGHTS_EXCP with
> (nolock) WHERE ACCOUNTID=@.ACCOUNTID
> AND PROVIDER_ID=@.PROVIDER_ID AND EMPID=@.PR
> if @.PR_VOICE_WEIGHT IS NOT NULL
> SET @.WEIGHTED_PR_DURATION = @.PR_VOICE_WEIGHT * @.DURATION
> --SELECT @.DICTATOR=FIRST_NAME + ' ' + LAST_NAME FROM PROVIDER WHERE
> PROVIDER_ID=@.PROVIDER_ID
> SELECT @.DICTATOR=DICTATED_BY FROM PROVIDER WHERE PROVIDER_ID=@.PROVIDER_ID
> SELECT @.FILE_ASSIGNED_DATE=dbo. sfd_fn_File_Assigned_Date(@.PR,getdate())
> SET @.FILE_ASSIGNED_DATE=CONVERT(VARCHAR(12),
@.FILE_ASSIGNED_DATE,101)
>
> BEGIN TRANSACTION
> IF @.LAST_PR IS NULL AND @.MaxSequence=1
> BEGIN
> UPDATE TR_TRANSCRIPTS with (updlock) SET ASSIGNED_TO_PR=@.PR WHERE
> VOICE_FILE_LOCATION=@.VOICE_FILE_LOCATION
> AND VOICE_FILE_NAME=@.VOICE_FILE_NAME
> If @.@.ERROR !=0
> BEGIN
> ROLLBACK TRANSACTION
> RETURN 0
> END
> UPDATE DISTRIBUTION with (updlock) SET
> ASSIGNED_TO_PR=@.PR,PR_SHIFT_ID=@.SHIFT_ID
,
> PR_ASSIGNED_TIME=GETDATE(),DISTRIBUTION_
TYPE=@.TYPE WHERE
> VOICE_FILE_LOCATION=@.VOICE_FILE_LOCATION
> AND VOICE_FILE_NAME=@.VOICE_FILE_NAME AND SEQUENCE=1
> If @.@.ERROR !=0
> BEGIN
> ROLLBACK TRANSACTION
> RETURN 0
> END
> UPDATE VOICE_FILE_PRIORITY with (updlock) SET ASSIGNED_TO_PR=@.PR WHERE
> VOICE_FILE_LOCATION=@.VOICE_FILE_LOCATION
> AND VOICE_FILE_NAME=@.VOICE_FILE_NAME
> If @.@.ERROR != 0
> BEGIN
> ROLLBACK TRANSACTION
> RETURN 0
> END
> END
> ELSE
> BEGIN
> SELECT @.TR_STATUS=TR_STATUS, @.TR=ASSIGNED_TO_TR, @.TR_SHIFT_ID=TR_SHIFT_ID,
> @.TR_ASSIGNED_TIME=TR_ASSIGNED_TIME
> FROM DISTRIBUTION with (nolock) WHERE sequence=1 AND
> VOICE_FILE_LOCATION=@.VOICE_FILE_LOCATION
> AND VOICE_FILE_NAME=@.VOICE_FILE_NAME
> INSERT INTO DISTRIBUTION (VOICE_FILE_LOCATION, VOICE_FILE_NAME,
> TR_STATUS,SEQUENCE,NOTES,PR_STATUS,ASSIG
NED_TO_TR,ASSIGNED_TO_PR,TR_SHIFT_
ID,PR_SHIFT_ID,TR_ASSIGNED_TIME,PR_ASSIG
NED_TIME,DISTRIBUTION_TYPE)
> values
> (@.VOICE_FILE_LOCATION, @.VOICE_FILE_NAME, @.TR_STATUS, @.MaxSequence+1,
> 'Distributed to Tr', 'N', @.TR, @.PR, @.TR_SHIFT_ID, @.SHIFT_ID,
> @.TR_ASSIGNED_TIME, GETDATE(),@.TYPE)
> -- SELECT @.COUNT=COUNT(*) FROM TR_TRANSCRIPTS WHERE PR_STATUS IN ('N','P')
> AND VOICE_FILE_LOCATION=@.VOICE_FILE_LOCATION
> -- AND VOICE_FILE_NAME=@.VOICE_FILE_NAME
> -- IF @.COUNT = 0
> -- BEGIN
> -- SELECT @.COUNT=COUNT(*) FROM TR_TRANSCRIPTS WHERE
> VOICE_FILE_LOCATION=@.VOICE_FILE_LOCATION
> -- AND VOICE_FILE_NAME=@.VOICE_FILE_NAME
> -- IF @.COUNT > 0
> -- BEGIN
> UPDATE VOICE_FILE_PRIORITY with (updlock) SET ASSIGNED_TO_PR=@.PR WHERE
> VOICE_FILE_LOCATION=@.VOICE_FILE_LOCATION
> AND VOICE_FILE_NAME=@.VOICE_FILE_NAME AND ASSIGNED_TO_PR IS NULL
> If @.@.ERROR != 0
> BEGIN
> ROLLBACK TRANSACTION
> RETURN 0
> END
> -- END
> -- END
> SET @.TR_TRANSCRIPTS = CURSOR FOR
> SELECT
> PATIENT_NAME,MRN,VISIT_DATE,TR_CHAR_COUN
T,PR_DOC_FILE_NAME,PR_DOC_FILE_LOC
ATION,VF_START_TIME,VF_END_TIME,TR_NOTES
,REPORT_TYPE,TR_CHECKIN_DATETIME,TR_
PL_STATUS,ASSIGNED_TO_TR,LOCATIONID
> FROM TR_TRANSCRIPTS with (nolock) where ASSIGNED_TO_PR=@.LAST_PR AND
> VOICE_FILE_LOCATION=@.VOICE_FILE_LOCATION
> AND VOICE_FILE_NAME=@.VOICE_FILE_NAME AND PR_DOC_FILE_NAME IS NOT NULL
> OPEN @.TR_TRANSCRIPTS
> FETCH NEXT FROM @.TR_TRANSCRIPTS INTO
> @.PATIENT_NAME,@.MRN,@.VISIT_DATE,@.TR_CHAR_
COUNT,@.PR_DOC_FILE_NAME,@.PR_DOC_FI
LE_LOCATION,@.VF_START_TIME,@.VF_END_TIME,
@.TR_NOTES,@.REPORT_TYPE,@.TR_CHECKIN_D
ATETIME,@.TR_PL_STATUS,@.TR,@.LOCATIONID
> WHILE (@.@.FETCH_STATUS = 0)
> BEGIN
> INSERT INTO
> TR_TRANSCRIPTS(DOC_FILE_NAME,DOC_FILE_LO
CATION,PATIENT_NAME,MRN,VISIT_DATE,TRANS
CR
IPTION_STATUS,TR_CHAR_COUNT,VOICE_FILE_N
AME,VOICE_FILE_LOCATION,ASSIGNED_TO_TR,T
R_CH
ECKIN_DATETIME,PR_STATUS,VF_START_TIME,V
F_END_TIME,TR_NOTES,REPORT_TYPE,TR_PL_ST
ATUS
,AS
SIGNED_TO_PR,LOCATIONID)
> VALUES
> (@.PR_DOC_FILE_NAME,@.PR_DOC_FILE_LOCATION
,@.PATIENT_NAME,@.MRN,@.VISIT_DATE,'A
',@.TR_CHAR_COUNT,@.VOICE_FILE_NAME,@.VOICE
_FILE_LOCATION,@.TR,@.TR_CHECKIN_DATET
IME,'N',@.VF_START_TIME,@.VF_END_TIME,@.TR_
NOTES,@.REPORT_TYPE,@.TR_PL_STATUS,@.PR
,@.LOCATIONID)
> SET @.undertranscription = 0
> SET @.readyforproofreading = @.Duration
> FETCH NEXT FROM @.TR_TRANSCRIPTS INTO
> @.PATIENT_NAME,@.MRN,@.VISIT_DATE,@.TR_CHAR_
COUNT,@.PR_DOC_FILE_NAME,@.PR_DOC_FI
LE_LOCATION,@.VF_START_TIME,@.VF_END_TIME,
@.TR_NOTES,@.REPORT_TYPE,@.TR_CHECKIN_D
ATETIME,@.TR_PL_STATUS,@.TR,@.LOCATIONID
> END
> CLOSE @.TR_TRANSCRIPTS
> DEALLOCATE @.TR_TRANSCRIPTS
>
> --UPDATE EMP_ASSIGNED_WORKLOAD
> END
> --common
> UPDATE VOICE_FILES with (updlock) SET STATUS='A',TYPE= CASE WHEN TYPE='B'
> OR TYPE='R' THEN 'N' ELSE TYPE END WHERE
> VOICE_FILE_LOCATION=@.VOICE_FILE_LOCATION
AND
> VOICE_FILE_NAME=@.VOICE_FILE_NAME
> If @.@.ERROR !=0
> BEGIN
> ROLLBACK TRANSACTION
> RETURN 0
> END
> IF EXISTS(SELECT empid from emp_assigned_workload with (nolock) WHERE
> EMPID=@.PR AND FILE_ASSIGNED_DATE=@.FILE_ASSIGNED_DATE
> AND SHIFT_ID=@.SHIFT_ID)
> UPDATE EMP_ASSIGNED_WORKLOAD with (updlock) SET
> UNDER_TRANSCRIPTION=UNDER_TRANSCRIPTION+
@.undertranscription,READY_FOR_PROO
FREADING=READY_FOR_PROOFREADING+@.readyfo
rproofreading,
> WORK_ASSIGNED=WORK_ASSIGNED+@.WEIGHTED_PR
_DURATION,
> PR_WORK_ASSIGNED=PR_WORK_ASSIGNED+@.DURAT
ION
> WHERE EMPID=@.PR AND FILE_ASSIGNED_DATE=@.FILE_ASSIGNED_DATE AND
> SHIFT_ID=@.SHIFT_ID
> ELSE
> INSERT INTO
> EMP_ASSIGNED_WORKLOAD(FILE_ASSIGNED_DATE
,EMPID,PR_WORK_COMPLETED,TR_WORK_C
OMPLETED,
> UNDER_TRANSCRIPTION,READY_FOR_PROOFREADI
NG,PR_WORK_ASSIGNED,TR_WORK_ASSIGN
ED,SHIFT_ID,
> WORK_ASSIGNED) VALUES (@.FILE_ASSIGNED_DATE,@.PR,0,0,@.undertrans
cription,
> @.readyforproofreading,@.DURATION,0,@.SHIFT
_ID,@.WEIGHTED_PR_DURATION)
> If @.@.ERROR !=0 OR @.@.ROWCOUNT=0
> BEGIN
> ROLLBACK TRANSACTION
> RETURN -10
> END
> COMMIT TRANSACTION
> RETURN 1
> GO
> --end 1st sp
> 2nd sp
> CREATE procedure dbo.sfd_sp_Assign_TR
> @.VOICE_FILE_LOCATION VARCHAR(255),
> @.VOICE_FILE_NAME VARCHAR(255),
> @.TR VARCHAR(10),
> @.ACCOUNTID varchar(10) output,
> @.DICTATOR VARCHAR(50) output
> AS
> DECLARE @.Count as int
> DECLARE @.PROVIDER_ID as smallint
> DECLARE @.WEIGHTED_TR_DURATION as int
> DECLARE @.DURATION as int
> DECLARE @.SHIFT_ID as varchar(10)
> DECLARE @.STAT_FLAG as char(1)
> DECLARE @.MAX_Priority as int
> DECLARE @.PRIORITY as bit
> DECLARE @.FILE_ASSIGNED_DATE as datetime
> DECLARE @.TRANS_VOICE_WEIGHT AS int
> DECLARE @.MASTER_BLOCKED AS int
> SELECT @.ACCOUNTID=ACCOUNTID,@.PROVIDER_ID=PROVID
ER_ID,@.DURATION=DURATION,
> @.WEIGHTED_TR_DURATION=WEIGHTED_TR_DURATI
ON,@.STAT_FLAG=STAT_FLAG FROM
> VOICE_FILES with (nolock) WHERE
> VOICE_FILE_LOCATION=@.VOICE_FILE_LOCATION
> AND VOICE_FILE_NAME=@.VOICE_FILE_NAME
> IF @.ACCOUNTID is NULL
> return -1 --No record in voice files
> SELECT @.Count=COUNT(*) FROM ACCOUNT_WEIGHTS WHERE ACCOUNTID=@.ACCOUNTID AND
> PROVIDER_ID=@.PROVIDER_ID
> If @.Count = 0
> return -2 --No record in account weights
> SELECT @.Count=count(*) from vf_tr_assign where
> VOICE_FILE_LOCATION=@.VOICE_FILE_LOCATION
> AND VOICE_FILE_NAME=@.VOICE_FILE_NAME
> If @.Count > 0
> return -3 --TR has already started working on the file
> SELECT @.SHIFT_ID=SHIFT_ID FROM EMP_SHIFT with (nolock) WHERE EMPID=@.TR AND
> getdate() >= EFFECTIVE_DATE AND DAY_OF_WEEK = datepart(dw,getdate())
> If @.SHIFT_ID is null
> return -4 --Shift ID not found
> SELECT @.Count=count(*) from DISTRIBUTION where
> VOICE_FILE_LOCATION=@.VOICE_FILE_LOCATION
> AND VOICE_FILE_NAME=@.VOICE_FILE_NAME
> If @.Count > 0
> return -5 --File already assigned for transcription
> if @.STAT_FLAG='S' or @.STAT_FLAG='P'
> SELECT @.MAX_Priority=MAX(PRIORITY) FROM VOICE_FILE_PRIORITY with (nolock)
> WHERE IMPORTANCE=@.STAT_FLAG
> ELSE
> BEGIN
> SELECT @.PRIORITY=PRIORITY FROM ACCOUNT with (nolock) WHERE
> ACCOUNTID=@.ACCOUNTID
> IF @.PRIORITY is null
> set @.PRIORITY=0
> IF @.PRIORITY = 1
> BEGIN
> SELECT @.MAX_Priority=MAX(PRIORITY) FROM VOICE_FILE_PRIORITY with (nolock)
> WHERE IMPORTANCE='N'
> set @.STAT_FLAG='N'
> END
> ELSE
> BEGIN
> SELECT @.MAX_Priority=MAX(PRIORITY) FROM VOICE_FILE_PRIORITY with (nolock)
> WHERE IMPORTANCE='L'
> set @.STAT_FLAG='L'
> END
> END
> if @.MAX_Priority is null
> set @.MAX_Priority = 0
> set @.MAX_Priority = @.MAX_Priority + 1
> SELECT @.TRANS_VOICE_WEIGHT=TRANS_VOICE_WEIGHT FROM ACCOUNT_WEIGHTS_EXCP
> with
> (nolock) WHERE ACCOUNTID=@.ACCOUNTID
> AND PROVIDER_ID=@.PROVIDER_ID AND EMPID=@.TR
> if @.TRANS_VOICE_WEIGHT is not null
> SET @.WEIGHTED_TR_DURATION = @.TRANS_VOICE_WEIGHT * @.DURATION
> --SELECT @.DICTATOR=FIRST_NAME + ' ' + LAST_NAME FROM PROVIDER WHERE
> PROVIDER_ID=@.PROVIDER_ID
> SELECT @.DICTATOR=DICTATED_BY FROM PROVIDER with (nolock) WHERE
> PROVIDER_ID=@.PROVIDER_ID
> SELECT @.FILE_ASSIGNED_DATE = dbo. sfd_fn_File_Assigned_Date(@.TR,getdate())
> SET @.FILE_ASSIGNED_DATE = CONVERT(VARCHAR(12),@.FILE_ASSIGNED_DATE,
101)
> BEGIN transaction
> Insert into
> dbo. Distribution(VOICE_FILE_LOCATION,VOICE_F
ILE_NAME,ASSIGNED_TO_TR,TR_SHI
FT_ID,NOTES,
> TR_ASSIGNED_TIME) values
> (@.VOICE_FILE_LOCATION,@.VOICE_FILE_NAME,@.
TR,@.SHIFT_ID
> ,'Distributed to Tr',GETDATE())
> If @.@.error != 0
> BEGIN
> rollback transaction
> RETURN 0
> END
> IF EXISTS(SELECT EMPID from emp_assigned_workload WHERE EMPID=@.TR AND
> FILE_ASSIGNED_DATE=@.FILE_ASSIGNED_DATE
> AND SHIFT_ID=@.SHIFT_ID)
> Update EMP_ASSIGNED_WORKLOAD with (updlock) SET
> WORK_ASSIGNED=WORK_ASSIGNED+@.WEIGHTED_TR
_DURATION,
> TR_WORK_ASSIGNED=TR_WORK_ASSIGNED+@.DURAT
ION WHERE EMPID=@.TR AND
> FILE_ASSIGNED_DATE=@.FILE_ASSIGNED_DATE
> AND SHIFT_ID=@.SHIFT_ID
> ELSE
> INSERT INTO
> EMP_ASSIGNED_WORKLOAD(FILE_ASSIGNED_DATE
,EMPID,PR_WORK_COMPLETED,TR_WORK_C
OMPLETED,
> UNDER_TRANSCRIPTION,READY_FOR_PROOFREADI
NG,PR_WORK_ASSIGNED,TR_WORK_ASSIGN
ED,SHIFT_ID,
> WORK_ASSIGNED) VALUES
> (@.FILE_ASSIGNED_DATE,@.TR,0,0,0,0,0,@.DURA
TION,@.SHIFT_ID,
> @.WEIGHTED_TR_DURATION)
> If @.@.error != 0 OR @.@.ROWCOUNT=0
> BEGIN
> rollback transaction
> RETURN -10
> END
> INSERT INTO
> VOICE_FILE_PRIORITY(VOICE_FILE_LOCATION,
VOICE_FILE_NAME,ACCOUNT_ID,PROVIDE
R_ID,
> PRIORITY,ASSIGNED_TO_TR,ASSIGNED_TO_PR,I
MPORTANCE,STATUS,PENDINGSTATUS)
> VALUES
> (@.VOICE_FILE_LOCATION,@.VOICE_FILE_NAME,@.
ACCOUNTID,@.PROVIDER_ID,
> @.MAX_Priority,@.TR ,NULL,@.STAT_FLAG,'N',NULL)
> If @.@.error != 0
> BEGIN
> rollback transaction
> RETURN 0
> END
> -- written by raghu for deadlock
> select @.MASTER_BLOCKED = blocked from dbo.master.sysprocesses with
> (nolock)
> where blocked<>0
> if @.MASTER_BLOCKED=0
> begin
> UPDATE VOICE_FILES with (updlock) SET STATUS='T',TYPE= CASE WHEN TYPE='B'
> OR TYPE='R' THEN 'N' ELSE TYPE END,UPDATE_TIME=getdate()
> WHERE VOICE_FILE_LOCATION=@.VOICE_FILE_LOCATION
AND
> VOICE_FILE_NAME=@.VOICE_FILE_NAME
> If @.@.error <> 0 or @.@.rowcount=0
> BEGIN
> rollback transaction
> RETURN 0
> END
> end
> else
> begin
> SET LOCK_TIMEOUT 30
> if @.@.LOCK_TIMEOUT= 30
> rollback transaction
> RETURN 0
> end
> -- end by raghu
>
>
> COMMIT TRANSACTION
> RETURN 1
> GO
>
> --end sp|||You should really be careful about using WITH(NOLOCK) when SELECTing
information that will be used in an INSERT or UPDATE. It can allow garbage
to be stored in the database.
At first glance, I notice a pattern that I would do different. I would
apply the update lock when you read the row to be updated. For example:
IF EXISTS(SELECT...FROM...WITH(UPDLOCK) WHERE...)
UPDATE...
With your code (depending on the transaction isolation level), the SELECT
will obtain a shared lock and then the UPDATE will attempt to obtain an
exclusive lock. This can cause a deadlock because more than one transaction
can obtain a shared lock, and then neither can obtain an exclusive lock
because both transactions have a shared lock.
You should avoid using a cursor within a transaction.
WITH(UPDLOCK) on a single-row update doesn't accomplish anything, because an
UPDATE always applies an exclusive lock on any row that is updated.
Also, there's nothing evil about using GOTO for error handling.
After further examination, these procedures are a disaster waiting to
happen. You really need to learn how locking works in SQL Server. You need
to learn about transaction isolation levels and how each affects lock
duration. You need to learn about how concurrent transactions
interact--especially when using WITH(NOLOCK). You also need to learn how to
write set-based statements instead of using cursors. In addition, you
should learn about optimistic concurrency, how to use rowversion (timestamp)
columns, and how to recover from collisions.
"raghu veer" <raghu veer@.discussions.microsoft.com> wrote in message
news:E05516F6-C1D4-418F-9111-C7EFD74B5BF3@.microsoft.com...
>i have 2 stored procedures
> 1st sp
> CREATE procedure dbo.sfd_sp_Assign_PR
> @.VOICE_FILE_LOCATION VARCHAR(255),
> @.VOICE_FILE_NAME VARCHAR(255),
> @.PR VARCHAR(10),
> @.TYPE CHAR(1),
> @.ACCOUNTID varchar(10) OUTPUT,
> @.DICTATOR VARCHAR(50) OUTPUT
> AS
> DECLARE @.Count as int
> DECLARE @.LAST_PR as VARCHAR(10)
> DECLARE @.TR as VARCHAR(10)
> DECLARE @.PROVIDER_ID as smallint
> DECLARE @.DURATION as int
> DECLARE @.SHIFT_ID as varchar(10)
> DECLARE @.TR_SHIFT_ID as varchar(10)
> DECLARE @.FILE_ASSIGNED_DATE as datetime
> DECLARE @.TR_ASSIGNED_TIME as datetime
> DECLARE @.undertranscription as int
> DECLARE @.readyforproofreading as int
> DECLARE @.MaxSequence as int
> DECLARE @.WEIGHTED_PR_DURATION as int
> DECLARE @.PR_VOICE_WEIGHT as int
> DECLARE @.TR_STATUS AS CHAR(1)
> DECLARE @.PR_DOC_FILE_NAME AS VARCHAR(255)
> DECLARE @.PR_DOC_FILE_LOCATION AS VARCHAR(255)
> DECLARE @.PATIENT_NAME AS VARCHAR(50)
> DECLARE @.MRN AS VARCHAR(20)
> DECLARE @.VISIT_DATE AS SMALLDATETIME
> DECLARE @.TR_CHAR_COUNT AS INT
> DECLARE @.VF_START_TIME AS SMALLINT
> DECLARE @.VF_END_TIME AS SMALLINT
> DECLARE @.TR_NOTES AS VARCHAR(1000)
> DECLARE @.TR_CHECKIN_DATETIME AS DATETIME
> DECLARE @.REPORT_TYPE AS VARCHAR(50)
> DECLARE @.TR_PL_STATUS AS SMALLINT
> DECLARE @.LOCATIONID AS SMALLINT
> DECLARE @.TR_TRANSCRIPTS CURSOR
> SELECT @.ACCOUNTID=ACCOUNTID,@.PROVIDER_ID=PROVID
ER_ID,@.DURATION=DURATION,
> @.WEIGHTED_PR_DURATION=WEIGHTED_PR_DURATI
ON FROM VOICE_FILES with (nolock)
> WHERE
> VOICE_FILE_LOCATION=@.VOICE_FILE_LOCATION
> AND VOICE_FILE_NAME=@.VOICE_FILE_NAME
> IF @.ACCOUNTID is NULL
> return -1 --No record in voice files
> SELECT @.Count=COUNT(*) FROM ACCOUNT_WEIGHTS with (nolock) WHERE
> ACCOUNTID=@.ACCOUNTID AND PROVIDER_ID=@.PROVIDER_ID
> If @.Count = 0
> return -2 --No record in account weights
> SELECT @.Count=count(*) from DISTRIBUTION with (nolock) where
> VOICE_FILE_LOCATION=@.VOICE_FILE_LOCATION
> AND VOICE_FILE_NAME=@.VOICE_FILE_NAME
> If @.Count = 0
> return -3 --File not assigned to TR for transcription
> SELECT @.Count=count(*) FROM VOICE_FILE_PRIORITY with (nolock) WHERE
> VOICE_FILE_NAME=@.VOICE_FILE_NAME AND
> VOICE_FILE_LOCATION=@.VOICE_FILE_LOCATION
> If @.Count = 0
> return -4 --No record in voice file priority
> SELECT assigned_to_pr FROM DISTRIBUTION with (nolock) WHERE
> VOICE_FILE_LOCATION=@.VOICE_FILE_LOCATION
> AND VOICE_FILE_NAME=@.VOICE_FILE_NAME and ASSIGNED_TO_PR=@.PR
> if @.@.rowcount=1
> return -5 --file already assigned to selected PR
> SELECT @.SHIFT_ID=SHIFT_ID FROM EMP_SHIFT with (nolock) WHERE EMPID=@.PR AND
> getdate() >= EFFECTIVE_DATE AND DAY_OF_WEEK = DATEPART(dw,getdate())
> If @.SHIFT_ID IS NULL
> return -6 --Shift ID not found
> SET @.undertranscription = 0
> SET @.readyforproofreading = 0
> SELECT @.MaxSequence=max(sequence) from distribution with (nolock) WHERE
> VOICE_FILE_LOCATION=@.VOICE_FILE_LOCATION
> AND VOICE_FILE_NAME=@.VOICE_FILE_NAME
> SELECT @.LAST_PR=ASSIGNED_TO_PR FROM DISTRIBUTION with (nolock) WHERE
> VOICE_FILE_LOCATION=@.VOICE_FILE_LOCATION
> AND VOICE_FILE_NAME=@.VOICE_FILE_NAME and sequence=@.MaxSequence
> IF @.MaxSequence=1 AND @.LAST_PR IS NULL
> BEGIN
> SELECT @.Count=COUNT(*) FROM VF_TR_ASSIGN with (nolock) WHERE STATUS in
> ('A','O') AND VOICE_FILE_LOCATION=@.VOICE_FILE_LOCATION
> AND VOICE_FILE_NAME=@.VOICE_FILE_NAME
> IF @.Count > 0
> SET @.undertranscription = @.Duration
> SELECT @.Count=COUNT(*) FROM VF_TR_ASSIGN with (nolock) WHERE STATUS='C'
> AND
> VOICE_FILE_LOCATION=@.VOICE_FILE_LOCATION
> AND VOICE_FILE_NAME=@.VOICE_FILE_NAME
> IF @.Count > 0
> SET @.readyforproofreading = @.Duration
> END
> SELECT @.PR_VOICE_WEIGHT=PR_VOICE_WEIGHT FROM ACCOUNT_WEIGHTS_EXCP with
> (nolock) WHERE ACCOUNTID=@.ACCOUNTID
> AND PROVIDER_ID=@.PROVIDER_ID AND EMPID=@.PR
> if @.PR_VOICE_WEIGHT IS NOT NULL
> SET @.WEIGHTED_PR_DURATION = @.PR_VOICE_WEIGHT * @.DURATION
> --SELECT @.DICTATOR=FIRST_NAME + ' ' + LAST_NAME FROM PROVIDER WHERE
> PROVIDER_ID=@.PROVIDER_ID
> SELECT @.DICTATOR=DICTATED_BY FROM PROVIDER WHERE PROVIDER_ID=@.PROVIDER_ID
> SELECT @.FILE_ASSIGNED_DATE=dbo. sfd_fn_File_Assigned_Date(@.PR,getdate())
> SET @.FILE_ASSIGNED_DATE=CONVERT(VARCHAR(12),
@.FILE_ASSIGNED_DATE,101)
>
> BEGIN TRANSACTION
> IF @.LAST_PR IS NULL AND @.MaxSequence=1
> BEGIN
> UPDATE TR_TRANSCRIPTS with (updlock) SET ASSIGNED_TO_PR=@.PR WHERE
> VOICE_FILE_LOCATION=@.VOICE_FILE_LOCATION
> AND VOICE_FILE_NAME=@.VOICE_FILE_NAME
> If @.@.ERROR !=0
> BEGIN
> ROLLBACK TRANSACTION
> RETURN 0
> END
> UPDATE DISTRIBUTION with (updlock) SET
> ASSIGNED_TO_PR=@.PR,PR_SHIFT_ID=@.SHIFT_ID
,
> PR_ASSIGNED_TIME=GETDATE(),DISTRIBUTION_
TYPE=@.TYPE WHERE
> VOICE_FILE_LOCATION=@.VOICE_FILE_LOCATION
> AND VOICE_FILE_NAME=@.VOICE_FILE_NAME AND SEQUENCE=1
> If @.@.ERROR !=0
> BEGIN
> ROLLBACK TRANSACTION
> RETURN 0
> END
> UPDATE VOICE_FILE_PRIORITY with (updlock) SET ASSIGNED_TO_PR=@.PR WHERE
> VOICE_FILE_LOCATION=@.VOICE_FILE_LOCATION
> AND VOICE_FILE_NAME=@.VOICE_FILE_NAME
> If @.@.ERROR != 0
> BEGIN
> ROLLBACK TRANSACTION
> RETURN 0
> END
> END
> ELSE
> BEGIN
> SELECT @.TR_STATUS=TR_STATUS, @.TR=ASSIGNED_TO_TR, @.TR_SHIFT_ID=TR_SHIFT_ID,
> @.TR_ASSIGNED_TIME=TR_ASSIGNED_TIME
> FROM DISTRIBUTION with (nolock) WHERE sequence=1 AND
> VOICE_FILE_LOCATION=@.VOICE_FILE_LOCATION
> AND VOICE_FILE_NAME=@.VOICE_FILE_NAME
> INSERT INTO DISTRIBUTION (VOICE_FILE_LOCATION, VOICE_FILE_NAME,
> TR_STATUS,SEQUENCE,NOTES,PR_STATUS,ASSIG
NED_TO_TR,ASSIGNED_TO_PR,TR_SHIFT_
ID,PR_SHIFT_ID,TR_ASSIGNED_TIME,PR_ASSIG
NED_TIME,DISTRIBUTION_TYPE)
> values
> (@.VOICE_FILE_LOCATION, @.VOICE_FILE_NAME, @.TR_STATUS, @.MaxSequence+1,
> 'Distributed to Tr', 'N', @.TR, @.PR, @.TR_SHIFT_ID, @.SHIFT_ID,
> @.TR_ASSIGNED_TIME, GETDATE(),@.TYPE)
> -- SELECT @.COUNT=COUNT(*) FROM TR_TRANSCRIPTS WHERE PR_STATUS IN ('N','P')
> AND VOICE_FILE_LOCATION=@.VOICE_FILE_LOCATION
> -- AND VOICE_FILE_NAME=@.VOICE_FILE_NAME
> -- IF @.COUNT = 0
> -- BEGIN
> -- SELECT @.COUNT=COUNT(*) FROM TR_TRANSCRIPTS WHERE
> VOICE_FILE_LOCATION=@.VOICE_FILE_LOCATION
> -- AND VOICE_FILE_NAME=@.VOICE_FILE_NAME
> -- IF @.COUNT > 0
> -- BEGIN
> UPDATE VOICE_FILE_PRIORITY with (updlock) SET ASSIGNED_TO_PR=@.PR WHERE
> VOICE_FILE_LOCATION=@.VOICE_FILE_LOCATION
> AND VOICE_FILE_NAME=@.VOICE_FILE_NAME AND ASSIGNED_TO_PR IS NULL
> If @.@.ERROR != 0
> BEGIN
> ROLLBACK TRANSACTION
> RETURN 0
> END
> -- END
> -- END
> SET @.TR_TRANSCRIPTS = CURSOR FOR
> SELECT
> PATIENT_NAME,MRN,VISIT_DATE,TR_CHAR_COUN
T,PR_DOC_FILE_NAME,PR_DOC_FILE_LOC
ATION,VF_START_TIME,VF_END_TIME,TR_NOTES
,REPORT_TYPE,TR_CHECKIN_DATETIME,TR_
PL_STATUS,ASSIGNED_TO_TR,LOCATIONID
> FROM TR_TRANSCRIPTS with (nolock) where ASSIGNED_TO_PR=@.LAST_PR AND
> VOICE_FILE_LOCATION=@.VOICE_FILE_LOCATION
> AND VOICE_FILE_NAME=@.VOICE_FILE_NAME AND PR_DOC_FILE_NAME IS NOT NULL
> OPEN @.TR_TRANSCRIPTS
> FETCH NEXT FROM @.TR_TRANSCRIPTS INTO
> @.PATIENT_NAME,@.MRN,@.VISIT_DATE,@.TR_CHAR_
COUNT,@.PR_DOC_FILE_NAME,@.PR_DOC_FI
LE_LOCATION,@.VF_START_TIME,@.VF_END_TIME,
@.TR_NOTES,@.REPORT_TYPE,@.TR_CHECKIN_D
ATETIME,@.TR_PL_STATUS,@.TR,@.LOCATIONID
> WHILE (@.@.FETCH_STATUS = 0)
> BEGIN
> INSERT INTO
> TR_TRANSCRIPTS(DOC_FILE_NAME,DOC_FILE_LO
CATION,PATIENT_NAME,MRN,VISIT_DATE,TRANS
CR
IPTION_STATUS,TR_CHAR_COUNT,VOICE_FILE_N
AME,VOICE_FILE_LOCATION,ASSIGNED_TO_TR,T
R_CH
ECKIN_DATETIME,PR_STATUS,VF_START_TIME,V
F_END_TIME,TR_NOTES,REPORT_TYPE,TR_PL_ST
ATUS
,AS
SIGNED_TO_PR,LOCATIONID)
> VALUES
> (@.PR_DOC_FILE_NAME,@.PR_DOC_FILE_LOCATION
,@.PATIENT_NAME,@.MRN,@.VISIT_DATE,'A
',@.TR_CHAR_COUNT,@.VOICE_FILE_NAME,@.VOICE
_FILE_LOCATION,@.TR,@.TR_CHECKIN_DATET
IME,'N',@.VF_START_TIME,@.VF_END_TIME,@.TR_
NOTES,@.REPORT_TYPE,@.TR_PL_STATUS,@.PR
,@.LOCATIONID)
> SET @.undertranscription = 0
> SET @.readyforproofreading = @.Duration
> FETCH NEXT FROM @.TR_TRANSCRIPTS INTO
> @.PATIENT_NAME,@.MRN,@.VISIT_DATE,@.TR_CHAR_
COUNT,@.PR_DOC_FILE_NAME,@.PR_DOC_FI
LE_LOCATION,@.VF_START_TIME,@.VF_END_TIME,
@.TR_NOTES,@.REPORT_TYPE,@.TR_CHECKIN_D
ATETIME,@.TR_PL_STATUS,@.TR,@.LOCATIONID
> END
> CLOSE @.TR_TRANSCRIPTS
> DEALLOCATE @.TR_TRANSCRIPTS
>
> --UPDATE EMP_ASSIGNED_WORKLOAD
> END
> --common
> UPDATE VOICE_FILES with (updlock) SET STATUS='A',TYPE= CASE WHEN TYPE='B'
> OR TYPE='R' THEN 'N' ELSE TYPE END WHERE
> VOICE_FILE_LOCATION=@.VOICE_FILE_LOCATION
AND
> VOICE_FILE_NAME=@.VOICE_FILE_NAME
> If @.@.ERROR !=0
> BEGIN
> ROLLBACK TRANSACTION
> RETURN 0
> END
> IF EXISTS(SELECT empid from emp_assigned_workload with (nolock) WHERE
> EMPID=@.PR AND FILE_ASSIGNED_DATE=@.FILE_ASSIGNED_DATE
> AND SHIFT_ID=@.SHIFT_ID)
> UPDATE EMP_ASSIGNED_WORKLOAD with (updlock) SET
> UNDER_TRANSCRIPTION=UNDER_TRANSCRIPTION+
@.undertranscription,READY_FOR_PROO
FREADING=READY_FOR_PROOFREADING+@.readyfo
rproofreading,
> WORK_ASSIGNED=WORK_ASSIGNED+@.WEIGHTED_PR
_DURATION,
> PR_WORK_ASSIGNED=PR_WORK_ASSIGNED+@.DURAT
ION
> WHERE EMPID=@.PR AND FILE_ASSIGNED_DATE=@.FILE_ASSIGNED_DATE AND
> SHIFT_ID=@.SHIFT_ID
> ELSE
> INSERT INTO
> EMP_ASSIGNED_WORKLOAD(FILE_ASSIGNED_DATE
,EMPID,PR_WORK_COMPLETED,TR_WORK_C
OMPLETED,
> UNDER_TRANSCRIPTION,READY_FOR_PROOFREADI
NG,PR_WORK_ASSIGNED,TR_WORK_ASSIGN
ED,SHIFT_ID,
> WORK_ASSIGNED) VALUES (@.FILE_ASSIGNED_DATE,@.PR,0,0,@.undertrans
cription,
> @.readyforproofreading,@.DURATION,0,@.SHIFT
_ID,@.WEIGHTED_PR_DURATION)
> If @.@.ERROR !=0 OR @.@.ROWCOUNT=0
> BEGIN
> ROLLBACK TRANSACTION
> RETURN -10
> END
> COMMIT TRANSACTION
> RETURN 1
> GO
> --end 1st sp
> 2nd sp
> CREATE procedure dbo.sfd_sp_Assign_TR
> @.VOICE_FILE_LOCATION VARCHAR(255),
> @.VOICE_FILE_NAME VARCHAR(255),
> @.TR VARCHAR(10),
> @.ACCOUNTID varchar(10) output,
> @.DICTATOR VARCHAR(50) output
> AS
> DECLARE @.Count as int
> DECLARE @.PROVIDER_ID as smallint
> DECLARE @.WEIGHTED_TR_DURATION as int
> DECLARE @.DURATION as int
> DECLARE @.SHIFT_ID as varchar(10)
> DECLARE @.STAT_FLAG as char(1)
> DECLARE @.MAX_Priority as int
> DECLARE @.PRIORITY as bit
> DECLARE @.FILE_ASSIGNED_DATE as datetime
> DECLARE @.TRANS_VOICE_WEIGHT AS int
> DECLARE @.MASTER_BLOCKED AS int
> SELECT @.ACCOUNTID=ACCOUNTID,@.PROVIDER_ID=PROVID
ER_ID,@.DURATION=DURATION,
> @.WEIGHTED_TR_DURATION=WEIGHTED_TR_DURATI
ON,@.STAT_FLAG=STAT_FLAG FROM
> VOICE_FILES with (nolock) WHERE
> VOICE_FILE_LOCATION=@.VOICE_FILE_LOCATION
> AND VOICE_FILE_NAME=@.VOICE_FILE_NAME
> IF @.ACCOUNTID is NULL
> return -1 --No record in voice files
> SELECT @.Count=COUNT(*) FROM ACCOUNT_WEIGHTS WHERE ACCOUNTID=@.ACCOUNTID AND
> PROVIDER_ID=@.PROVIDER_ID
> If @.Count = 0
> return -2 --No record in account weights
> SELECT @.Count=count(*) from vf_tr_assign where
> VOICE_FILE_LOCATION=@.VOICE_FILE_LOCATION
> AND VOICE_FILE_NAME=@.VOICE_FILE_NAME
> If @.Count > 0
> return -3 --TR has already started working on the file
> SELECT @.SHIFT_ID=SHIFT_ID FROM EMP_SHIFT with (nolock) WHERE EMPID=@.TR AND
> getdate() >= EFFECTIVE_DATE AND DAY_OF_WEEK = datepart(dw,getdate())
> If @.SHIFT_ID is null
> return -4 --Shift ID not found
> SELECT @.Count=count(*) from DISTRIBUTION where
> VOICE_FILE_LOCATION=@.VOICE_FILE_LOCATION
> AND VOICE_FILE_NAME=@.VOICE_FILE_NAME
> If @.Count > 0
> return -5 --File already assigned for transcription
> if @.STAT_FLAG='S' or @.STAT_FLAG='P'
> SELECT @.MAX_Priority=MAX(PRIORITY) FROM VOICE_FILE_PRIORITY with (nolock)
> WHERE IMPORTANCE=@.STAT_FLAG
> ELSE
> BEGIN
> SELECT @.PRIORITY=PRIORITY FROM ACCOUNT with (nolock) WHERE
> ACCOUNTID=@.ACCOUNTID
> IF @.PRIORITY is null
> set @.PRIORITY=0
> IF @.PRIORITY = 1
> BEGIN
> SELECT @.MAX_Priority=MAX(PRIORITY) FROM VOICE_FILE_PRIORITY with (nolock)
> WHERE IMPORTANCE='N'
> set @.STAT_FLAG='N'
> END
> ELSE
> BEGIN
> SELECT @.MAX_Priority=MAX(PRIORITY) FROM VOICE_FILE_PRIORITY with (nolock)
> WHERE IMPORTANCE='L'
> set @.STAT_FLAG='L'
> END
> END
> if @.MAX_Priority is null
> set @.MAX_Priority = 0
> set @.MAX_Priority = @.MAX_Priority + 1
> SELECT @.TRANS_VOICE_WEIGHT=TRANS_VOICE_WEIGHT FROM ACCOUNT_WEIGHTS_EXCP
> with
> (nolock) WHERE ACCOUNTID=@.ACCOUNTID
> AND PROVIDER_ID=@.PROVIDER_ID AND EMPID=@.TR
> if @.TRANS_VOICE_WEIGHT is not null
> SET @.WEIGHTED_TR_DURATION = @.TRANS_VOICE_WEIGHT * @.DURATION
> --SELECT @.DICTATOR=FIRST_NAME + ' ' + LAST_NAME FROM PROVIDER WHERE
> PROVIDER_ID=@.PROVIDER_ID
> SELECT @.DICTATOR=DICTATED_BY FROM PROVIDER with (nolock) WHERE
> PROVIDER_ID=@.PROVIDER_ID
> SELECT @.FILE_ASSIGNED_DATE = dbo. sfd_fn_File_Assigned_Date(@.TR,getdate())
> SET @.FILE_ASSIGNED_DATE = CONVERT(VARCHAR(12),@.FILE_ASSIGNED_DATE,
101)
> BEGIN transaction
> Insert into
> dbo. Distribution(VOICE_FILE_LOCATION,VOICE_F
ILE_NAME,ASSIGNED_TO_TR,TR_SHI
FT_ID,NOTES,
> TR_ASSIGNED_TIME) values
> (@.VOICE_FILE_LOCATION,@.VOICE_FILE_NAME,@.
TR,@.SHIFT_ID
> ,'Distributed to Tr',GETDATE())
> If @.@.error != 0
> BEGIN
> rollback transaction
> RETURN 0
> END
> IF EXISTS(SELECT EMPID from emp_assigned_workload WHERE EMPID=@.TR AND
> FILE_ASSIGNED_DATE=@.FILE_ASSIGNED_DATE
> AND SHIFT_ID=@.SHIFT_ID)
> Update EMP_ASSIGNED_WORKLOAD with (updlock) SET
> WORK_ASSIGNED=WORK_ASSIGNED+@.WEIGHTED_TR
_DURATION,
> TR_WORK_ASSIGNED=TR_WORK_ASSIGNED+@.DURAT
ION WHERE EMPID=@.TR AND
> FILE_ASSIGNED_DATE=@.FILE_ASSIGNED_DATE
> AND SHIFT_ID=@.SHIFT_ID
> ELSE
> INSERT INTO
> EMP_ASSIGNED_WORKLOAD(FILE_ASSIGNED_DATE
,EMPID,PR_WORK_COMPLETED,TR_WORK_C
OMPLETED,
> UNDER_TRANSCRIPTION,READY_FOR_PROOFREADI
NG,PR_WORK_ASSIGNED,TR_WORK_ASSIGN
ED,SHIFT_ID,
> WORK_ASSIGNED) VALUES
> (@.FILE_ASSIGNED_DATE,@.TR,0,0,0,0,0,@.DURA
TION,@.SHIFT_ID,
> @.WEIGHTED_TR_DURATION)
> If @.@.error != 0 OR @.@.ROWCOUNT=0
> BEGIN
> rollback transaction
> RETURN -10
> END
> INSERT INTO
> VOICE_FILE_PRIORITY(VOICE_FILE_LOCATION,
VOICE_FILE_NAME,ACCOUNT_ID,PROVIDE
R_ID,
> PRIORITY,ASSIGNED_TO_TR,ASSIGNED_TO_PR,I
MPORTANCE,STATUS,PENDINGSTATUS)
> VALUES
> (@.VOICE_FILE_LOCATION,@.VOICE_FILE_NAME,@.
ACCOUNTID,@.PROVIDER_ID,
> @.MAX_Priority,@.TR ,NULL,@.STAT_FLAG,'N',NULL)
> If @.@.error != 0
> BEGIN
> rollback transaction
> RETURN 0
> END
> -- written by raghu for deadlock
> select @.MASTER_BLOCKED = blocked from dbo.master.sysprocesses with
> (nolock)
> where blocked<>0
> if @.MASTER_BLOCKED=0
> begin
> UPDATE VOICE_FILES with (updlock) SET STATUS='T',TYPE= CASE WHEN TYPE='B'
> OR TYPE='R' THEN 'N' ELSE TYPE END,UPDATE_TIME=getdate()
> WHERE VOICE_FILE_LOCATION=@.VOICE_FILE_LOCATION
AND
> VOICE_FILE_NAME=@.VOICE_FILE_NAME
> If @.@.error <> 0 or @.@.rowcount=0
> BEGIN
> rollback transaction
> RETURN 0
> END
> end
> else
> begin
> SET LOCK_TIMEOUT 30
> if @.@.LOCK_TIMEOUT= 30
> rollback transaction
> RETURN 0
> end
> -- end by raghu
>
>
> COMMIT TRANSACTION
> RETURN 1
> GO
>
> --end sp