"Sergey Karimov" <OverDrone_Trash@mail.ru> wrote in message
news:Oku60%2384GHA.3376@TK2MSFTNGP05.phx.gbl...
>I have question about RAISERROR command.
> Look at the example above. first select shows one record in table, that
> means that transaction was committed. second select shows that transaction
> was not committed.
> My intention is raising exception similar to PK constraint violation. As
> you see from this example RAISERROR doesn't fit.
> How can I raise such exception?
>
> Usage of T-SQL "try-catch" is inappropriate because the only purpuse of
> RAISERROR is generate an exception and rollback transaction, there is no
> specific action required for exception, only rollback transaction.
> Writing tons of code (begin tran/commit tran, try-catch) in any stored
> procedure just to rollback transaction in case of built-in or custom
> exception isn't a good idea.
>
> SQL:
>
> create table test_table(id int primary key)
> go
>
> create procedure test_proc1
> as
> set xact_abort on
> begin tran
> insert into test_table(id)
> values(1)
> raiserror('test_error',18,1)
> print 'committing'
> commit tran
> print 'committed'
> go
>
> create procedure test_proc2
> as
> set xact_abort on
> begin tran
> insert into test_table(id)
> values(1)
> insert into test_table(id)
> values(1)
> print 'committing'
> commit tran
> print 'committed'
> go
>
> delete from test_table
> exec test_proc1
> select * from test_table
> go
>
> delete from test_table
> exec test_proc2
> select * from test_table
> go
>
>