First Transaction - Help ! - 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 2000 Forums

Transact-SQL (2000)

First Transaction - Help !

Author

Topic

ank2go
Starting Member

6 Posts

Posted - 2004-06-24 : 16:37:15

Hi !

Thanks in advance for reading this post.

This is my first transaction, so i want to do it right.

Here it is:

create procedure dbo.happy as
BEGIN
begin transaction

update test0
set provid = '005'
where provid = '004'

if @@error <> 0 goto err_handler

update test02
set provid = '005'
where provid = '004'

if @@error <> 0 goto err_handler

update test03
set provid = '005'
where provid = '004'

if @@error <> 0 goto err_handler

commit transaction
return (0)

err_handler:
rollback transaction
raiserror ('Failed Stored Procedure1.',16,1)
return (99)

END

I intentionally update a non-existing table (test0), so that
I could test my err_handler. The really table should be test01.

When I execute happy I get the following error:

Server: Msg 266, Level 16, State 1, Procedure happy, Line 18
Transaction count after EXECUTE indicates that a COMMIT or ROLLBACK TRANSACTION
statement is missing. Previous count = 8, current count = 9.

I want to know from you database guru, if my SP is what you would do.

Or Should I change my SP to something like this:

create procedure dbo.happy as BEGIN
begin transaction

update test0
set provid = '005'
where provid = '004'

IF @@ERROR <> 0
BEGIN
ROLLBACK TRAN
return 10
END

update test02
set provid = '005'
where provid = ''

IF @@ERROR <> 0
BEGIN
ROLLBACK TRAN
return 11
END

update test03
set provid = '005'
where provid = ''

IF @@ERROR <> 0
BEGIN
ROLLBACK TRAN
return 12
END

commit transaction
return (0)

END

Thanks in advance for your replies !

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2004-06-24 : 16:42:53

Your stored procedure looks good. Either one would work though. It just depends on your preference.

Tara

ank2go
Starting Member

6 Posts

Posted - 2004-06-24 : 16:48:59

Thanks Tara for your quick reply.

Please indulge me with another question.

I've tried looking into SQL Server Books Online
for @@error, and was not able to find anything that
would explain this:

raiserror ('Failed Stored Procedure1.',16,1)

What does the parameter means: 16 and 1

and when I have the above error, why it
didn't print 'Failed Stored Procedure1' and
it printed this:

Transaction count after EXECUTE indicates that a COMMIT or ROLLBACK
TRANSACTION statement is missing. Previous count = 8, current count = 9.

AGain thanks !

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2004-06-24 : 16:52:18

16 is the state and 1 is the severity of RAISERROR.

quote:

severity

Is the user-defined severity level associated with this message. Severity levels from 0 through 18 can be used by any user. Severity levels from 19 through 25 are used only by members of the sysadmin fixed server role. For severity levels from 19 through 25, the WITH LOG option is required.

Caution Severity levels from 20 through 25 are considered fatal. If a fatal severity level is encountered, the client connection is terminated after receiving the message, and the error is logged in the error log and the application log.

state

Is an arbitrary integer from 1 through 127 that represents information about the invocation state of the error. A negative value for state defaults to 1.

You aren't getting the message because the error is so severe that it doesn't make it past the first update statement. You might want to play around with violations of foreign keys to get the message to print, although maybe even that is severe enough to cause it not to go further. Use a real table that has no data in it. Run IF @@ROWCOUNT = 0 RAISERROR...

That'll show you the message.

Tara

ank2go
Starting Member

6 Posts

Posted - 2004-06-24 : 16:54:34

Got it. Thanks !

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources