Procedure gets hang...not able to execute normally

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Procedure gets hang...not able to execute normally

Author

Topic

rajnidas
Yak Posting Veteran

97 Posts

Posted - 2015-02-07 : 00:38:10

HI EVERYONE,

BELOW IS PART OF MY PROCEDURE WHERE I HAVE ISSUE. WHEN I EXECUTING THE PROCEDURE AT CLIENT SIDE, OUR PROCEDURE GETS HANG AND TABLES GETS LOCK.
WHEN WE STOP THE PROCEDURE FORCEFULLY WE COME TO KNOW THAT OUR PROCEDURE STOPPED AT "OPEN CUR" STATMENT.

PLEASE I NEED HELP...

Thanks in advance.

CREATE PROCEDURE COMPUTE

AS BEGIN

BEGIN TRAN
/*
WE ARE INSERTING DATA IN #TEMP1 HERE
*/
INSERT INTO [MAINMASTER](LEID, TP_FILE_REF, CLIENTNAME, ARM_CODE, CRG_CODE, TEAMID, CONTINUED_FLAG, NPA_AGING, WORKFLOW_FLAG, EFFECTIVE_DATE, ON_ACCOUNT_OF)
SELECT
C.LEID
,STUFF((
SELECT DISTINCT '| '
+ IC.FILETYPE_ID +':'+ IC.FILEID FROM #TEMP1 AS IC
WHERE IC.LEID = C.LEID
FOR XML PATH (''))
,1,2,'') AS TP_REF_FILE
,C.CLIENTNAME
, C.RM_CODE AS RM_CODE,
C.CRG_CODE,
C.TEAMID,
1 AS CONTINUED_FLAG,
MAX(C.NPA_AGING),
10 AS WORKFLOW_FLAG
,@UPLOAD_DATE
,STUFF((SELECT ', '
+ IC.FILE_DESCRIPTION FROM #TEMP1 AS IC
WHERE IC.LEID = C.LEID
GROUP BY IC.FILE_DESCRIPTION
FOR XML PATH('')),1,2,'') AS ON_ACCOUNT_OF
FROM #TEMP1 AS C
GROUP BY C.LEID,C.CLIENTNAME,C.RM_CODE,C.CRG_CODE,C.TEAMID--,C.NPA_AGING

ORDER BY C.LEID,C.CLIENTNAME,C.RM_CODE,C.CRG_CODE

PRINT 'MAIN_MASTER'
SET @TRACING = 'MAIN_MASTER'

-----------IF ALL FILES NOT HAVING HOLIDAY----------------------------------------
PRINT 'IF ALL FILES NOT HAVING HOLIDAY'
SET @TRACING = 'IF ALL FILES NOT HAVING HOLIDAY'
INSERT INTO @HMTABLE(FILETYPEID)
SELECT HFTM.FILE_TYPE_ID FROM SCB_EMPDM.DBO.HOLIDAY_MASTER AS HM WITH (NOLOCK)
INNER JOIN SCB_EMPDM.DBO.HOLIDAY_FILETYPEMAPPING AS HFTM WITH (NOLOCK) ON HFTM.HOLIDAY_ID = HM.ID AND HM.DATE = CONVERT(DATE,@UPLOAD_DATE,121)

print '@@ROWCOUNT' + @@ROWCOUNT
SET @ROWSTOPROCESS = @@ROWCOUNT
SET @CURRENTROW = 0
PRINT 'START CUR'
DECLARE CUR CURSOR FAST_FORWARD FOR SELECT MM.ID, MM.TP_FILE_REF FROM MAIN_MASTER AS MM WITH (NOLOCK) WHERE MM.EFFECTIVE_DATE = DATEADD(DAY,-1,CONVERT(DATE,@UPLOAD_DATE,121)) AND MM.CONTINUED_FLAG IN(0,1,3) AND MM.LEID NOT IN(
SELECT MMM.LEID FROM MAIN_MASTER AS MMM WITH (NOLOCK) WHERE MMM.EFFECTIVE_DATE = CONVERT(DATE,@UPLOAD_DATE,121))
OPEN CUR -----/*Issue we are facing*/
PRINT 'CUR OPEN'
FETCH NEXT FROM CUR INTO @MAINMASTERID, @TPREF
WHILE(@@FETCH_STATUS = 0)
BEGIN
WHILE @CURRENTROW < @ROWSTOPROCESS
BEGIN
IF @FLAG = 0
BEGIN
SET @CURRENTROW = @CURRENTROW + 1
END
ELSE
BEGIN
SET @FLAG = 0
END
SELECT
@CHAR = '|' + cast(FILETYPEID as varchar(20)) + ':'
FROM @HMTABLE
WHERE ROWID = @CURRENTROW

SET @TPREF = '|'+ @TPREF
IF CHARINDEX(@CHAR,@TPREF,1) > 0
BEGIN
INSERT INTO #MAIN_MASTERNPA (MAINMASTERID) SELECT @MAINMASTERID
SET @FLAG = 1
FETCH NEXT FROM CUR INTO @MAINMASTERID, @TPREF
END

IF @CURRENTROW = @ROWSTOPROCESS
BEGIN
INSERT INTO #MAIN_MASTERNPADROP (MAINMASTERID) SELECT @MAINMASTERID
END
END
SET @CURRENTROW = 0
FETCH NEXT FROM CUR INTO @MAINMASTERID, @TPREF
END
CLOSE CUR
DEALLOCATE CUR
PRINT 'CUR CLOSE'

COMMIT TRAN
END

Regards,
Rajni das.
DBA

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2015-02-08 : 07:41:09

Why are you using a cursor for this? Show us some sample data to illustrate what the code is doing. We should be able to remove that cursor.

Tara Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources