Item Updated OR error occured? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

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

Old Forums

CLOSED - General SQL Server

Item Updated OR error occured?

Author

Topic

MikeB
Constraint Violating Yak Guru

387 Posts

Posted - 2005-02-23 : 13:06:33

How can I tell if an update for an item occurred?
Currently in my s'procs I use the following :


IF(@@ERROR <> 0 OR @@ROWCOUNT = 0)
        RAISERROR('Failed to update item or record doesn't exist',16,1)

But this doesn't seem to be a good test. I executed a stored proc on an item that doesn't exist and no error message was sent back!

Any thoughts?
Mike B

X002548
Not Just a Number

15586 Posts

Posted - 2005-02-23 : 13:33:05

Well a sproc with that in it wouldn't even compile

And I would usually separate those 2 error types out


USE Northwind
GO

SET NOCOUNT ON
CREATE TABLE myTable99(Col1 int IDENTITY(1,1), Col2 char(1))
GO

INSERT INTO myTable99(Col2)
SELECT 'a' UNION ALL
SELECT 'b' UNION ALL
SELECT 'c'
GO

CREATE PROC mySproc99 (@Col1 int, @Col2 char(1))
AS

UPDATE myTable99 SET Col2 = @Col2 WHERE Col1 = @Col1

IF(@@ERROR <> 0 OR @@ROWCOUNT = 0)
        RAISERROR('Failed to update item or record doesnt exist',16,1)
Return
GO

EXEC mySproc99 1,'x'

EXEC mySproc99 4,'x'
GO

SET NOCOUNT OFF
DROP PROC mySproc99
DROP TABLE myTable99
GO

Have a look at

http://weblogs.sqlteam.com/brettk/archive/2004/05/25/1378.aspx

Brett

8-)

MikeB
Constraint Violating Yak Guru

387 Posts

Posted - 2005-02-23 : 13:36:26

quote:
Originally posted by X002548

Well a sproc with that in it wouldn't even compile

And I would usually separate those 2 error types out

USE Northwind
GO

SET NOCOUNT ON
CREATE TABLE myTable99(Col1 int IDENTITY(1,1), Col2 char(1))
GO

INSERT INTO myTable99(Col2)
SELECT 'a' UNION ALL
SELECT 'b' UNION ALL
SELECT 'c'
GO

CREATE PROC mySproc99 (@Col1 int, @Col2 char(1))
AS

UPDATE myTable99 SET Col2 = @Col2 WHERE Col1 = @Col1

IF(@@ERROR <> 0 OR @@ROWCOUNT = 0)
RAISERROR('Failed to update item or record doesnt exist',16,1)
Return
GO

EXEC mySproc99 1,'x'

EXEC mySproc99 4,'x'
GO

SET NOCOUNT OFF
DROP PROC mySproc99
DROP TABLE myTable99
GO

Have a look at

http://weblogs.sqlteam.com/brettk/archive/2004/05/25/1378.aspx

Brett

8-)

Why wouldn't it compile? Not sure why you say this?

Mike B

X002548
Not Just a Number

15586 Posts

Posted - 2005-02-23 : 13:46:57

Your apostrophe will cause it to fail

IF(@@ERROR <> 0 OR @@ROWCOUNT = 0)
RAISERROR('Failed to update item or record doesn't exist',16,1)

Brett

8-)

MikeB
Constraint Violating Yak Guru

387 Posts

Posted - 2005-02-23 : 14:15:57

quote:
Originally posted by X002548

Your apostrophe will cause it to fail

IF(@@ERROR <> 0 OR @@ROWCOUNT = 0)
RAISERROR('Failed to update item or record doesn't exist',16,1)

Brett

8-)

LOL, thanks, didn't even notice that!

Mike B :)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources