Update failing - 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)

Update failing

Author

Topic

TRACEYSQL
Aged Yak Warrior

594 Posts

Posted - 2007-12-05 : 10:06:46

Im updating table1 - Update did not get generated some value missing and i do not want it to to got table2

But it goes to TABLE2.

How to know if update TABLE1 was successful or not...

UPDATE TABLE1 where x = '123'

--Have error check it
IF @@ERROR <>0
BEGIN
SET @ERROR_CODE = 'UPDATE TABLE ERROR'
END

--Ony here do i want to do next update
IF @@ERROR = 0
BEGIN
UPDATE TABLE2 where x = '123'
END

sshelper
Posting Yak Master

216 Posts

Posted - 2007-12-05 : 10:20:40

Instead of using 2 IF statements, use an ELSE clause:

UPDATE TABLE1 where x = '123'

--Have error check it
IF @@ERROR <>0
BEGIN
SET @ERROR_CODE = 'UPDATE TABLE ERROR'
END
--Ony here do i want to do next update
ELSE IF @@ERROR = 0
BEGIN
UPDATE TABLE2 where x = '123'
END

The reason is because the @@ERROR is cleared and reset on each statement executed. Since your code executed a SET statement, the @@ERROR gets a value of 0 after that.

SQL Server Helper
http://www.sql-server-helper.com

TRACEYSQL
Aged Yak Warrior

594 Posts

Posted - 2007-12-05 : 10:37:11

The table1 did not get updated as the condition where was not met.
So im assuming the error is 0 as its not error just never got updated.
But do not want to continue...on table 2.

BEGIN
--Update table 1
Update TABLE1
IF @@ERROR <>0
BEGIN
SET @ERROR_CODE = 'TABLE ERROR'
END
ELSE IF @@ERROR = 0
BEGIN
print 'here'
UPDATE TABLE2
END

TRACEYSQL
Aged Yak Warrior

594 Posts

Posted - 2007-12-05 : 11:00:55

I got it i did
BEGIN
--Update table 1
Update TABLE1
set @check_rowcount = @@ROWCOUNT
IF @@ERROR <>0
BEGIN
SET @ERROR_CODE = 'TABLE ERROR'
END
ELSE IF @@ERROR = 0 AND @CHECK_ROWCOUNT > 0 (added this so if updated the rowcount be 1)
BEGIN
print 'here'
UPDATE TABLE2
END

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-12-05 : 11:11:37

What kind of syntax is this?

Also @@ERROR is highly volatile, so once referenced (as in an IF check) the value is reset.

DECLARE	@ErrorCode VARCHAR(200)

UPDATE	Table1 
SET	xxx = 'yyy'
WHERE	zzz = 4

IF @@ERROR <> 0
	SET @ErrorCode = 'TABLE ERROR'

IF @ErrorCode IS NULL
	UPDATE	Table2
	SET	xxx = 'yyy'
	WHERE	zzz = 4

E 12°55'05.25"
N 56°04'39.16"

TRACEYSQL
Aged Yak Warrior

594 Posts

Posted - 2007-12-05 : 12:24:25

Thanks i do what your doing reset to a variable and check the variable ...

X002548
Not Just a Number

15586 Posts

Posted - 2007-12-05 : 13:23:02

quote:
Originally posted by Peso

What kind of syntax is this?

Also @@ERROR is highly volatile, so once referenced (as in an IF check) the value is reset.
DECLARE @ErrorCode VARCHAR(200)

UPDATE Table1
SET xxx = 'yyy'
WHERE zzz = 4

IF @@ERROR <> 0
SET @ErrorCode = 'TABLE ERROR'

IF @ErrorCode IS NULL
UPDATE Table2
SET xxx = 'yyy'
WHERE zzz = 4

E 12°55'05.25"
N 56°04'39.16"

Peso, Peso, Peso


DECLARE	@ErrorCode VARCHAR(200), @error int, @rowcount int

UPDATE	Table1 
SET	xxx = 'yyy'
WHERE	zzz = 4

SELECT @error = @@Error, @rowcount = @@rowcount

IF @ERROR <> 0
  BEGIN
	SET @ErrorCode = 'TABLE ERROR'
  END

IF @ERROR = 0
  BEGIN
	UPDATE	Table2
	SET	xxx = 'yyy'
	WHERE	zzz = 4
  END

IF @rowcount = 0
	SET @ErrorCode = 'NO ROWS MODIFIED'

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Add yourself!
http://www.frappr.com/sqlteam

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-12-05 : 13:32:30

Oh, OP wants a second criteria?
Only if first update went well (no errors) AND at least one record got updated, the second update should execute?

I interpreted it as the second update should execute as long as there were no errors in the first update (regardless of how many records that were updated).

E 12°55'05.25"
N 56°04'39.16"

TRACEYSQL
Aged Yak Warrior

594 Posts

Posted - 2007-12-05 : 13:53:46

Cheers

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources