Catching errors in a cursor - SQL Server Forums

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

SQL Server 2000 Forums

SQL Server Development (2000)

Catching errors in a cursor

Author

Topic

ini686
Starting Member

4 Posts

Posted - 2002-05-15 : 09:41:12

I am using a cursor to update a table row by row and when I try to catch errors using @@error after the update it continues to the next row. I am putting code in to exceed the data type but it actually truncates it and continues. Can errors be trapped with in a cursor update?

J-

robvolk
Most Valuable Yak

15732 Posts

Posted - 2002-05-15 : 09:49:34

Well, why are you using a cursor? Why not just a single UPDATE statement? It's a hell of a lot easier, faster and more efficient.

Can you post the code you have now? It's hard to troubleshoot without seeing it.

ini686
Starting Member

4 Posts

Posted - 2002-05-15 : 10:01:01

I need to select a column from each row and run another SP with the value selected to regenerate the value.

WHILE @@FETCH_STATUS = 0
BEGIN
SET @tmpWebCode = LEFT(@tmpWebCode, 5) + @newKeyCode
EXEC @checkDigit = spGetCheckDigit 0, @tmpWebCode

SET @tmpWebCode = UPPER(@tmpWebCode + @checkDigit)

UPDATE products SET webcode = @tmpWebcode
WHERE CURRENT OF webcodes
IF @@ERROR <> 0 GOTO ERROR

FETCH NEXT FROM webcodes INTO @tmpWebcode

END

This help?

robvolk
Most Valuable Yak

15732 Posts

Posted - 2002-05-15 : 10:12:09

Can you post the code for spGetCheckDigit? Sorry.

ini686
Starting Member

4 Posts

Posted - 2002-05-15 : 10:53:56

It's a doozie.......

@returnValue INT OUTPUT,
@itemKey varchar (9)

AS

--declare @itemKey varchar (10)
declare @count int
declare @char char(1)
declare @checkDigit varchar(2)

declare @EvenTemp varchar(5)
declare @EvenIndex int
declare @iEven int

declare @OddTemp varchar(5)
declare @OddIndex int
declare @iOdd int

set @EvenIndex=1
set @OddIndex=1
--set @itemKey = '093890MXV'
set @count = 1
set @EvenTemp = ' '
set @OddTemp = ' '

WHILE 1=1
BEGIN

SET @char = SUBSTRING(@itemkey, @count, 1)

IF ISNUMERIC(@char) = 0
BEGIN
SELECT @char =
CASE @char
WHEN 'A' THEN 1 WHEN 'J' THEN 1
WHEN 'B' THEN 2 WHEN 'K' THEN 2 WHEN 'S' THEN 2
WHEN 'C' THEN 3 WHEN 'L' THEN 3 WHEN 'T' THEN 3
WHEN 'D' THEN 4 WHEN 'M' THEN 4 WHEN 'U' THEN 4
WHEN 'E' THEN 5 WHEN 'N' THEN 5 WHEN 'V' THEN 5
WHEN 'F' THEN 6 WHEN 'O' THEN 6 WHEN 'W' THEN 6
WHEN 'G' THEN 7 WHEN 'P' THEN 7 WHEN 'X' THEN 7
WHEN 'H' THEN 8 WHEN 'Q' THEN 8 WHEN 'Y' THEN 8
WHEN 'I' THEN 9 WHEN 'R' THEN 9 WHEN 'Z' THEN 9
ELSE 0
END
END

IF @count % 2 = 0
BEGIN
SET @EvenTemp = STUFF(@Eventemp, @Evenindex, 1, @char)
SET @EvenIndex = @EvenIndex + 1
END
ELSE
BEGIN
SET @OddTemp = STUFF(@Oddtemp, @Oddindex, 1, @char)
SET @OddIndex = @OddIndex + 1
END

IF LEN(@OddTemp) >= 5
BEGIN
SET @iEven = (cast(@EvenTemp AS INT) * 2)
SET @EvenTemp = CONVERT(VARCHAR(5), @iEven)
SET @iEven = CAST(SUBSTRING(@EvenTemp, 1, 1) AS INT) + CAST(SUBSTRING(@EvenTemp, 2, 1) AS INT) + CAST(SUBSTRING(@EvenTemp, 3, 1) AS INT) + CAST(SUBSTRING(@EvenTemp, 4, 1) AS INT) + CAST(SUBSTRING(@EvenTemp, 5, 1) AS INT)
-- print @iEven

SET @iOdd = CAST(SUBSTRING(@OddTemp, 1, 1) AS INT) + CAST(SUBSTRING(@OddTemp, 2, 1) AS INT) + CAST(SUBSTRING(@OddTemp, 3, 1) AS INT) + CAST(SUBSTRING(@OddTemp, 4, 1) AS INT) + CAST(SUBSTRING(@OddTemp, 5, 1) AS INT)
-- PRINT @iOdd
SET @checkDigit = (CONVERT(VARCHAR(2), @iOdd + @iEven))
SET @checkDigit = 10 - (SUBSTRING(@checkDigit, 2, 1))

--If check digit is 10 or greater convert it to ZERO
IF @checkDigit >= 10
BEGIN
SET @checkDigit = 0
END

--print @checkDigit
END

SET @count = @count + 1

IF @count > LEN(@itemKey)
BREAK
ELSE
CONTINUE

END

set @returnValue = @checkDigit

Return @returnValue
GO

robvolk
Most Valuable Yak

15732 Posts

Posted - 2002-05-15 : 11:50:41

OK, so much for my shot at making this pure SQL...

Um, could it be that you're trying to add/concantenate a string with a numeric, without CONVERTing them explicitly:

SET @tmpWebCode = LEFT(@tmpWebCode, 5) + @newKeyCode
...
SET @tmpWebCode = UPPER(@tmpWebCode + @checkDigit)

That could throw an error that is easily avoided:

SET @tmpWebCode = LEFT(@tmpWebCode, 5) + @newKeyCode
...
SET @tmpWebCode = UPPER(@tmpWebCode + CAST(@checkDigit as Varchar))

Failing that, what kind of errors are you getting? Do the values violate constraints or foreign keys or something? What would cause errors? Do they happen in the update or in the spGetCheckDigit procedure? I think the error might actually be in the procedure, not the update. It would be better to try to eliminate the causes, then you don't have to trap errors. Put some error testing/handling in the check digit procedure, so that no matter what happens, it will always return a value.

Also, if you have SQL 2000, you might be able to write this as a user-defined function and eliminate the cursor. At least you won't have to trap errors row-by-row.

ini686
Starting Member

4 Posts

Posted - 2002-05-15 : 12:50:16

My problem is not with the sprocs, they work fine....

The problem is with this line...
UPDATE products SET webcode = @tmpWebcode
WHERE CURRENT OF webcodes
IF @@ERROR <> 0 GOTO ERROR

This does not catch errors.....???

Even when I exceed that data type intentionally it truncates the data
continues...

???

Page47
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2002-05-15 : 13:09:39

quote:

My problem is not with the sprocs, they work fine....

The problem is with this line...
UPDATE products SET webcode = @tmpWebcode
WHERE CURRENT OF webcodes
IF @@ERROR <> 0 GOTO ERROR

This does not catch errors.....???

Even when I exceed that data type intentionally it truncates the data
continues...

???

I don't believe sql considers truncation an 'error', rather it's just a 'warning. Why not



UPDATE products SET webcode = @tmpWebcode 
WHERE CURRENT OF webcodes 
and len(isnull(@tmpWebcode,'')) > 0

IF @@ERROR <> 0 or @@ROWCOUNT = 0 GOTO ERROR

<O>

robvolk
Most Valuable Yak

15732 Posts

Posted - 2002-05-15 : 13:31:16

quote:
Even when I exceed that data type intentionally it truncates the data
continues...

OK, here's where I have a problem: why exceed the limits intentionally? If you want that reported as an error, you should do as Page47 suggests: test for it. You could use RAISERROR if you want the code to stop processing at that point.

I'm confused, do you want the UPDATE to succeed, or are you deliberately causing errors for testing purposes?

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources