Use rowsAffected in Query - 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 2005 Forums

Transact-SQL (2005)

Use rowsAffected in Query

Author

Topic

melon.melon
Yak Posting Veteran

76 Posts

Posted - 2009-06-21 : 23:08:46

Hello


BEGIN TRANSACTION
  Delete a from t1 a left join t2 b on b.no=a.no
  left join t3 c on c.no=a.no where a.no=@no 
  and b.no is null and c.no is null
      IF @@ERROR=0
    BEGIN
     Insert into t1Deleted (no,Size,Owner,Users)
     values
     (@no,@size,@owner,@user);
   IF @@ERROR=0 
    BEGIN
    COMMIT TRANSACTION
    END
   ELSE
    BEGIN
       ROLLBACK TRANSACTION
       RAISEERROR('Delete Failed',1,1)
       Insert into tbLog (ErrorType,Msg)
       values
       ('Delete Failure','Deletion of Vessel Failed');
   IF @@ERROR<>0 
    BEGIN
    RAISEERROR('Delete Failed',1,1)
    END
END
END

I want to modify the above code such that:
when no rows affected for the delete query, it will insert an error msg into tbLog where deletion fails
else it will insert the deleted rows into t1Deleted.
Something like this:


BEGIN TRANSACTION
  Delete a from t1 a left join t2 b on b.no=a.no
  left join t3 c on c.no=a.no where a.no=@no 
  and b.no is null and c.no is null
 
if rowsAffected = 0

  Insert into tbLog (ErrorType,Msg)
       values
       ('Delete Failure','Deletion of Vessel Failed');
else

Insert into t1Deleted (no,Size,Owner,Users)
     values
     (@no,@size,@owner,@user);

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2009-06-21 : 23:37:42

you mean @@rowcount ?

KH
[spoiler]Time is always against us[/spoiler]

melon.melon
Yak Posting Veteran

76 Posts

Posted - 2009-06-22 : 00:07:40

hi khtan,
Got this error - Transaction count after EXECUTE indicates that a COMMIT or ROLLBACK TRANSACTION statement is missing. Previous count = 0, current count = 1.

BEGIN TRANSACTION
Delete a from t1 a left join t2 b on b.no=a.no
left join t3 c on c.no=a.no where a.no=@no
and b.no is null and c.no is null

END
IF @@ROWCOUNT=0
BEGIN
Insert into tbLog (ErrorType,Msg)
values ('Delete Failure','Deletion of Vessel Failed');
IF @@ROWCOUNT=0
BEGIN
COMMIT TRANSACTION
END
ELSE
BEGIN
ROLLBACK TRANSACTION
END
IF @@rowCount>0
BEGIN
Insert into t1Deleted (no,Size,Owner,Users)
values(@no,@size,@owner,@user);
IF @@ROWCOUNT>0
BEGIN
COMMIT TRANSACTION
END
ELSE
BEGIN
ROLLBACK TRANSACTION
END
END
END
END

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources