Doubt in SP & Raise Error - 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

General SQL Server Forums

New to SQL Server Programming

Doubt in SP & Raise Error

Author

Topic

mageshks
Yak Posting Veteran

59 Posts

Posted - 2007-01-30 : 11:08:48

Will raiserror within a procedure make a auto rollback

I have a SP like

Create procedure test
as
begin tran
insert into test values (1,'one')
raiseerror('%s',16,1,'Value Inserted')
commit tran

will the above SP make the insertion or not?.if not,pls tell me how to make the value insert into the table still
raiserror is needed.

Thanks in advance

nr
SQLTeam MVY

12543 Posts

Posted - 2007-01-30 : 11:16:44

Yes it will do the insert as you have committed the transaction.
begin tran
insert into test values (1,'one')
raiseerror('%s',16,1,'Value Inserted')
rollback tran

Note - for a singel statement you don't need a transaction as every statement is atomic. If it fails it will rollback and raise an error for you.

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2007-01-30 : 11:17:06

Severity level above 18 will cause auto rollback.

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources