A new @@Error question.

Here is what I really need to accomplish:
CREATE TABLE [dbo].[Table1] (
[c1] [char] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[Table2] (
[c1] [char] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY]
GO
declare @.Error1 int
declare @.Error2 int
begin transaction
drop table Table1
select @.Error1 = @.@.Error
exec sp_rename 'Table3','Table1'
select @.Error2 = @.@.Error
if @.Error1 = 0
if @.Error2 = 0
commit transaction
else
rollback transaction
Im not understanding why Table1 still gets dropped? It seems to me like
@.@.Error2 should not be 0, therefore a Rollback should occur? Why is @.@.Error2
not 0?
TIA,
ChrisR
> Im not understanding why Table1 still gets dropped? It seems to me like
> @.@.Error2 should not be 0, therefore a Rollback should occur? Why is
> @.@.Error2
> not 0?
@.@.ERROR returns the error number of the last Transact-SQL statement
executed. You need to also check the proc return code to determine whether
or not the rename succeeded:
declare @.Error1 int
declare @.Error2 int
declare @.ReturnCode int
begin transaction
drop table Table1x
select @.Error1 = @.@.Error
exec @.ReturnCode = sp_rename 'Table3','Table1'
select @.Error2 = @.@.Error
if @.Error1 = 0
if @.Error2 = 0 AND @.ReturnCode = 0
commit transaction
else
rollback transaction
Hope this helps.
Dan Guzman
SQL Server MVP
"ChrisR" <ChrisR@.discussions.microsoft.com> wrote in message
news:1A3F5C0A-9EC9-492D-9205-04031D36BFAA@.microsoft.com...
> Here is what I really need to accomplish:
> CREATE TABLE [dbo].[Table1] (
> [c1] [char] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
> ) ON [PRIMARY]
> GO
> CREATE TABLE [dbo].[Table2] (
> [c1] [char] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
> ) ON [PRIMARY]
> GO
> declare @.Error1 int
> declare @.Error2 int
> begin transaction
> drop table Table1
> select @.Error1 = @.@.Error
> exec sp_rename 'Table3','Table1'
> select @.Error2 = @.@.Error
> if @.Error1 = 0
> if @.Error2 = 0
> commit transaction
> else
> rollback transaction
> Im not understanding why Table1 still gets dropped? It seems to me like
> @.@.Error2 should not be 0, therefore a Rollback should occur? Why is
> @.@.Error2
> not 0?
> --
> TIA,
> ChrisR
|||Look at the procedure code. Then look at the documentation regarding the
raiserror function. Then look at the documentation for @.@.error. The
following demonstrates why this happens. As Dan suggested, you also need
to refer to the documentation of the procedure to know how to use it.
set nocount on
go
create procedure xx_test @.sev int
as
RAISERROR ('Testing raiserror with following statement', @.sev, 20)
return 1
go
exec xx_test -1
select @.@.error
exec xx_test 10
select @.@.error
exec xx_test 11
select @.@.error
go
alter procedure xx_test @.sev int
as
RAISERROR ('Testing raiserror, no following statement', @.sev, 20)
go
exec xx_test -1
select @.@.error
exec xx_test 10
select @.@.error
exec xx_test 11
select @.@.error
go
drop procedure xx_test
go