How to test transaction? - 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)

How to test transaction?

Author

Topic

Sun Foster
Aged Yak Warrior

515 Posts

Posted - 2010-02-09 : 10:05:07

I used SPtrantest below to test transaction.
How to create an error to test and print error code?
I tried to rename a column name from MYNAME to MYNAMEX and run "exec sptrantest 1" but it only displays another error message without eroor code.
---------------
ALTER PROCEDURE [dbo].[SPtrantest]
@id INT
AS
-- STEP 1
BEGIN TRANSACTION

DECLARE @myerror INT

UPDATE _aaa
SET myname = 'QQQ'
WHERE id = @id

SET @myerror = @@ERROR

PRINT @myerror

IF @@ERROR <> 0
BEGIN
PRINT @myerror

ROLLBACK TRANSACTION

RAISERROR('ERROR FROM SP',
16,
1)

RETURN
END

COMMIT
--------------- TABLE _AAA

ID MYNAME CODE
----------------------------
1 AAA 111
2 BBB 222

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-02-09 : 10:19:49

keep a constraint on column and then insert data to violate it

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2010-02-09 : 10:23:53

And
IF @@ERROR <> 0
should be
IF @myerror <> 0
because @@ERROR changes each time a command is executed. So it will be zero because "PRINT @myerror"-command was successful.

No, you're never too old to Yak'n'Roll if you're too young to die.

Kristen
Test

22859 Posts

Posted - 2010-02-09 : 10:36:19

Note that

IF @@ERROR <> 0

is only valid immediately after the statement you want to test, for a test at this point in the code you will need to use

IF @myerror <> 0

instead

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2010-02-09 : 10:41:35

I have already posted this

No, you're never too old to Yak'n'Roll if you're too young to die.

Sun Foster
Aged Yak Warrior

515 Posts

Posted - 2010-02-09 : 10:52:02

Thank all of you.

Kristen
Test

22859 Posts

Posted - 2010-02-09 : 10:55:31

Yeah, had opened the thread to answer, phone rang, answered ... phone rang again ... only now seen that you had already answered.

Some of us have two jobs

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2010-02-09 : 10:56:27

only two

No, you're never too old to Yak'n'Roll if you're too young to die.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources