[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

Why would this stored procedure fail sometimes

Posted on 2011-10-19
9
Medium Priority
?
611 Views
Last Modified: 2012-05-12
Hello

I cannot understand why a stored procedure that I wrote does not always complete correctly.  I could not understand why some of the records were not processed so I added a log file to the process and I insert 1 record with a -1 at the beginning of the process and insert another record with a -9999 in it at the end of the process so I can check on it.  
With this log I can see that on 3 occasions out of say 20 the times it was run the process did add the 2nd (-9999) record upon completion.
If I execute the process manually a few seconds later or a day later the process runs correctly and the records that were not processed are processed correctly.
So, my question is, what is anything in my stored procedure could cause the procedure to abend abnormally and not insert the 2nd record.
I have attached the entire procedure and some screen shots, one screen shot sorted by the sort field showing the first record in, some processed records but missing the end of process record and another screen shot showing successful entries.

Any help would be creatly appreciated.
TIA


 Picture showing log with missing transaction Picture showing log with missing transaction Picture showing log with normal transactions
ALTER PROCEDURE [dbo].[SP_PAY_PREP] AS

DECLARE @PSL_KEY NUMERIC(11,0) 
DECLARE @PRJ_FLD3 VARCHAR(20)
DECLARE @CHH_CODE VARCHAR(100)
DECLARE @EMH_CODE VARCHAR(10)
DECLARE @RAT_FLD2 VARCHAR(8)
DECLARE @PSL_CNT NUMERIC(11)
DECLARE @ROW_CNT NUMERIC(11)

DECLARE @PSL_ISIP1 VARCHAR(4)
DECLARE @PSL_ISIP2 VARCHAR(7)
DECLARE @PSL_ISIP3 VARCHAR(15)
DECLARE @PSL_ISIP4 VARCHAR(3)
DECLARE @PSL_ISIP5 VARCHAR(10)
DECLARE @PSL_ISIP7 VARCHAR(20)

DECLARE @ISI_SORT NUMERIC(11)
DECLARE @ISI_TSTAMP DATETIME

SELECT @ISI_SORT=COUNT(*) FROM ISILOG
IF @ISI_SORT=0 
	SET @ISI_SORT = 1
ELSE
BEGIN
	SELECT @ISI_SORT=MAX(isnull(ISI_SORT,0)) FROM ISILOG
	IF @ISI_SORT=NULL
		SET @ISI_SORT = 1
	ELSE
		SET @ISI_SORT = @ISI_SORT + 1
END

SET @ISI_TSTAMP=getdate()
INSERT INTO ISILOG (ISI_UID,ISI_SORT,ISI_TSTAMP,ISI_PSLKEY) 
VALUES (newid(),@ISI_SORT,@ISI_TSTAMP,-1)
SET @ISI_SORT = @ISI_SORT + 1

-- Static columns
SET @PSL_ISIP1 =  '3116'
SET @PSL_ISIP2 =  'COSTING'
SET @PSL_ISIP4 =  'REG'

SET CURSOR_CLOSE_ON_COMMIT OFF

SELECT @PSL_CNT=COUNT(*) FROM PSLINES, PSHEADER, EMPHIS, CHRHIS, TCPROJ, RATE
WHERE (PSLINES.PSL_PAYFLAG <> 'T' OR PSLINES.PSL_PAYFLAG IS NULL) 
        AND PSL_PSH=PSH_KEY AND PSH_EMH=EMH_KEY AND PSL_CHH=CHH_KEY AND PSL_PRJ=PRJ_KEY AND PSL_RAT_CD=RAT_CODE

DECLARE PSL_CURSOR CURSOR READ_ONLY FORWARD_ONLY
FOR SELECT PSL_KEY, PRJ_FLD3, CHH_CODE, EMH_CODE, RAT_FLD2
FROM PSLINES, PSHEADER, EMPHIS, CHRHIS, TCPROJ, RATE
WHERE (PSLINES.PSL_PAYFLAG <> 'T' OR PSLINES.PSL_PAYFLAG IS NULL) 
        AND PSL_PSH=PSH_KEY AND PSH_EMH=EMH_KEY AND PSL_CHH=CHH_KEY AND PSL_PRJ=PRJ_KEY AND PSL_RAT_CD=RAT_CODE

OPEN PSL_CURSOR
SET @ROW_CNT = 1
WHILE @ROW_CNT <= @PSL_CNT 
BEGIN
	FETCH NEXT FROM PSL_CURSOR INTO @PSL_KEY, @PRJ_FLD3, @CHH_CODE, @EMH_CODE, @RAT_FLD2
    SET @ROW_CNT = @ROW_CNT + 1


	-- Set the Employee Code
	SET @PSL_ISIP3 = @EMH_CODE

	-- Set the RAT_FLD2
	SET @PSL_ISIP5 = @RAT_FLD2

	-- Set the GL value = the value in the PRJ_FLD3 column
	SET @PSL_ISIP7 = @PRJ_FLD3

	-- These are some exceptions to deal with GL Code, otherwise use the PRJ_FLD3 value
	IF @CHH_CODE='Z TO.VAC'  
	BEGIN
		SET @PSL_ISIP7 = '2-1155'
		SET @PSL_ISIP5 = 'L'
	END

	IF (@CHH_CODE='Z TO.BANK') OR (@CHH_CODE='Z TO.TIME OFF')  
	BEGIN
		SET @PSL_ISIP7 = '2-1156'
		SET @PSL_ISIP5 = 'X4'
	END	
      
	IF @CHH_CODE='Z TO.PATERN'  
		SET @PSL_ISIP7 = '0'

	IF (@CHH_CODE='Z TO.STAT') OR (@CHH_CODE='Z TO.PERMITTED')  
	BEGIN
		SET @PSL_ISIP7 = '5-1010'
		SET @PSL_ISIP5 = 'R'
	END

	BEGIN TRANSACTION 
	UPDATE PSLINES SET 
		PSL_ISIP1 = @PSL_ISIP1,
		PSL_ISIP2 = @PSL_ISIP2,
		PSL_ISIP3 = @PSL_ISIP3,
		PSL_ISIP4 = @PSL_ISIP4,
		PSL_ISIP5 = @PSL_ISIP5,
		PSL_ISIP7 = @PSL_ISIP7,
		PSL_PAYFLAG = 'T'  WHERE PSL_KEY = @PSL_KEY 

	IF  @@ERROR <> 0 
		ROLLBACK TRANSACTION
	ELSE 
		COMMIT TRANSACTION


    SET @ISI_TSTAMP=getdate()
	INSERT INTO ISILOG (ISI_UID,ISI_SORT,ISI_TSTAMP,ISI_PSLKEY) 
	VALUES (newid(),@ISI_SORT,@ISI_TSTAMP,@PSL_KEY)
	SET @ISI_SORT = @ISI_SORT + 1

END
-- Close and release the cursor
CLOSE PSL_CURSOR
DEALLOCATE PSL_CURSOR
SET @ISI_TSTAMP=getdate()
INSERT INTO ISILOG (ISI_UID,ISI_SORT,ISI_TSTAMP,ISI_PSLKEY) 
VALUES (newid(),@ISI_SORT,@ISI_TSTAMP,-9999)

Open in new window

0
Comment
Question by:hmstechsupport
  • 5
  • 4
9 Comments
 
LVL 39

Expert Comment

by:BrandonGalderisi
ID: 36995702
I don't see any error handling other than the transaction rollback.  What happens when other errors occur?
0
 

Author Comment

by:hmstechsupport
ID: 36995805
None.  Being a newbie...what other kind of errors could occur in that loop and how would I best add error handling that would keep the loop going?
0
 
LVL 39

Expert Comment

by:BrandonGalderisi
ID: 36995861
You're not doing anything that would require using a cursor and you are not using the cursor correctly.  You are relying on a counter to fetch records until the end but you don't rely on @@fetch_status to ensure the state of the cursor.  There is no reason this can't be done using a single UPDATE statement and avoiding the cursor or looping all together.
0
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

Author Comment

by:hmstechsupport
ID: 36995925
Would the state of the cursor be a reason for this to fail?  Since I cannot see a way to code this in a single SQL statement I could use the @@fetch_status instead.
Would that be better?
0
 
LVL 39

Expert Comment

by:BrandonGalderisi
ID: 36995978
You are doing a count, and relying on the data set to not change and alter your working set for the cursor.  It's hard to help because of the way your query is structured for your cursor declaration.  You are using T-SQL join syntax instead of ANSI syntax (table,table instead of table join table) and you have no aliases or table qualifiers for your columns so that i can help you re-write it.
0
 

Author Comment

by:hmstechsupport
ID: 36996038
Ok, I've made the changes as follows idetifying the aliases and changed to use @@fetch_status.
Would this work better?

ALTER PROCEDURE [dbo].[SP_PAY_PREP] AS

DECLARE @PSL_KEY NUMERIC(11,0)
DECLARE @PRJ_FLD3 VARCHAR(20)
DECLARE @CHH_CODE VARCHAR(100)
DECLARE @EMH_CODE VARCHAR(10)
DECLARE @RAT_FLD2 VARCHAR(8)

DECLARE @PSL_ISIP1 VARCHAR(4)
DECLARE @PSL_ISIP2 VARCHAR(7)
DECLARE @PSL_ISIP3 VARCHAR(15)
DECLARE @PSL_ISIP4 VARCHAR(3)
DECLARE @PSL_ISIP5 VARCHAR(10)
DECLARE @PSL_ISIP7 VARCHAR(20)

DECLARE @ISI_SORT NUMERIC(11)
DECLARE @ISI_TSTAMP DATETIME

SELECT @ISI_SORT=COUNT(*) FROM ISILOG
IF @ISI_SORT=0
      SET @ISI_SORT = 1
ELSE
BEGIN
      SELECT @ISI_SORT=MAX(isnull(ISI_SORT,0)) FROM ISILOG
      IF @ISI_SORT=NULL
            SET @ISI_SORT = 1
      ELSE
            SET @ISI_SORT = @ISI_SORT + 1
END

SET @ISI_TSTAMP=getdate()
INSERT INTO ISILOG (ISI_UID,ISI_SORT,ISI_TSTAMP,ISI_PSLKEY)
VALUES (newid(),@ISI_SORT,@ISI_TSTAMP,-1)
SET @ISI_SORT = @ISI_SORT + 1

-- Static columns
SET @PSL_ISIP1 =  '3116'
SET @PSL_ISIP2 =  'COSTING'
SET @PSL_ISIP4 =  'REG'

SET CURSOR_CLOSE_ON_COMMIT OFF

DECLARE PSL_CURSOR CURSOR READ_ONLY FORWARD_ONLY
FOR SELECT A.PSL_KEY, E.PRJ_FLD3, D.CHH_CODE, C.EMH_CODE, F.RAT_FLD2
FROM PSLINES A, PSHEADER B, EMPHIS C, CHRHIS D, TCPROJ E, RATE F
WHERE (A.PSL_PAYFLAG <> 'T' OR A.PSL_PAYFLAG IS NULL)
        AND A.PSL_PSH=B.PSH_KEY AND B.PSH_EMH=C.EMH_KEY AND A.PSL_CHH=D.CHH_KEY AND A.PSL_PRJ=E.PRJ_KEY AND A.PSL_RAT_CD=F.RAT_CODE

OPEN PSL_CURSOR
FETCH NEXT FROM PSL_CURSOR INTO @PSL_KEY, @PRJ_FLD3, @CHH_CODE, @EMH_CODE, @RAT_FLD2
WHILE @@fetch_status = 0  
BEGIN
      -- Set the Employee Code
      SET @PSL_ISIP3 = @EMH_CODE

      -- Set the RAT_FLD2
      SET @PSL_ISIP5 = @RAT_FLD2

      -- Set the GL value = the value in the PRJ_FLD3 column
      SET @PSL_ISIP7 = @PRJ_FLD3

      -- These are some exceptions to deal with GL Code, otherwise use the PRJ_FLD3 value
      IF @CHH_CODE='Z TO.VAC'  
      BEGIN
            SET @PSL_ISIP7 = '2-1155'
            SET @PSL_ISIP5 = 'L'
      END

      IF (@CHH_CODE='Z TO.BANK') OR (@CHH_CODE='Z TO.TIME OFF')  
      BEGIN
            SET @PSL_ISIP7 = '2-1156'
            SET @PSL_ISIP5 = 'X4'
      END      
     
      IF @CHH_CODE='Z TO.PATERN'  
            SET @PSL_ISIP7 = '0'

      IF (@CHH_CODE='Z TO.STAT') OR (@CHH_CODE='Z TO.PERMITTED')  
      BEGIN
            SET @PSL_ISIP7 = '5-1010'
            SET @PSL_ISIP5 = 'R'
      END

      BEGIN TRANSACTION
      UPDATE PSLINES SET
            PSL_ISIP1 = @PSL_ISIP1,
            PSL_ISIP2 = @PSL_ISIP2,
            PSL_ISIP3 = @PSL_ISIP3,
            PSL_ISIP4 = @PSL_ISIP4,
            PSL_ISIP5 = @PSL_ISIP5,
            PSL_ISIP7 = @PSL_ISIP7,
            PSL_PAYFLAG = 'T'  WHERE PSL_KEY = @PSL_KEY

      IF  @@ERROR <> 0
            ROLLBACK TRANSACTION
      ELSE
            COMMIT TRANSACTION


    SET @ISI_TSTAMP=getdate()
      INSERT INTO ISILOG (ISI_UID,ISI_SORT,ISI_TSTAMP,ISI_PSLKEY)
      VALUES (newid(),@ISI_SORT,@ISI_TSTAMP,@PSL_KEY)
      SET @ISI_SORT = @ISI_SORT + 1
      FETCH NEXT FROM PSL_CURSOR INTO @PSL_KEY, @PRJ_FLD3, @CHH_CODE, @EMH_CODE, @RAT_FLD2

END
-- Close and release the cursor
CLOSE PSL_CURSOR
DEALLOCATE PSL_CURSOR
SET @ISI_TSTAMP=getdate()
INSERT INTO ISILOG (ISI_UID,ISI_SORT,ISI_TSTAMP,ISI_PSLKEY)
VALUES (newid(),@ISI_SORT,@ISI_TSTAMP,-9999)
0
 
LVL 39

Accepted Solution

by:
BrandonGalderisi earned 2000 total points
ID: 36996139
I believe I have captured all of the logic of your cursor in the below statement.  Verify it's accuracy before using in production.
UPDATE A
SET 
      PSL_ISIP1 = '3116'
     ,PSL_ISIP2 = 'COSTING'
     ,PSL_ISIP3 = C.EMH_CODE
     ,PSL_ISIP4 = 'REG'
     ,PSL_ISIP5 = CASE   WHEN d.CHH_CODE IN ('Z TO.STAT','Z TO.PERMITTED') THEN 'R'
                         WHEN d.CHH_CODE IN ('Z TO.BANK','Z TO.TIME OFF') THEN 'X4'
                         WHEN D.CHH_CODE = 'Z TO.VAC' THEN 'L'
                         ELSE F.RAT_FLD2 
                  END 
     ,PSL_ISIP7 = CASE   WHEN d.CHH_CODE IN ('Z TO.STAT','Z TO.PERMITTED') THEN '5-1010'
                         WHEN d.CHH_CODE IN ('Z TO.BANK','Z TO.TIME OFF') THEN '2-1156'
                         WHEN D.CHH_CODE = 'Z TO.VAC' THEN 'L'
                         ELSE E.PRJ_FLD3
                  END 
     ,PSL_PAYFLAG = 'T'
FROM
    PSLINES A
      INNER JOIN PSHEADER B
        ON A.PSL_PSH = B.PSH_KEY
      INNER JOIN EMPHIS C
        ON B.PSH_EMH = C.EMH_KEY
      INNER JOIN CHRHIS D
        ON A.PSL_CHH = D.CHH_KEY
      INNER JOIN TCPROJ E
        ON A.PSL_PRJ = E.PRJ_KEY
      INNER JOIN RATE F
        ON A.PSL_RAT_CD = F.RAT_CODE
WHERE
    ( A.PSL_PAYFLAG <> 'T'
      OR A.PSL_PAYFLAG IS NULL
    )

Open in new window

0
 

Author Comment

by:hmstechsupport
ID: 36996263
Wow.  That's pretty amazing!
It will take me a few hours to validate this but upon visual it looks like it should do the trick.

Thanks
0
 

Author Closing Comment

by:hmstechsupport
ID: 37025552
Thanks.
I made a frew corrections in the SQL but the idea was perfect.
0

Featured Post

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Ever wondered why sometimes your SQL Server is slow or unresponsive with connections spiking up but by the time you go in, all is well? The following article will show you how to install and configure a SQL job that will send you email alerts includ…
Ready to get certified? Check out some courses that help you prepare for third-party exams.
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.
Suggested Courses

834 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question