Stored Procedures and transactions...

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)

Stored Procedures and transactions...

Author

Topic

dve
Starting Member

19 Posts

Posted - 2005-08-27 : 16:52:09

Hi,

Is it possible to perform some CRUD operations in a stored procedure outside of the context of the transaction it is called in?

BEGIN TRAN

EXEC mySproc

ROLLBACK TRAN

mySproc's CRUD operations are not rolled back

Thanks!

Kristen
Test

22859 Posts

Posted - 2005-08-28 : 03:13:47

I reckon anything in mySproc will be rolled back (EXCEPT anything in an @TempTable, not sure about #TempTable).

What's CRUD? (I'm probably just being thick!)

Kristen

kapilarya
Yak Posting Veteran

86 Posts

Posted - 2005-08-28 : 03:19:52

try this :
you will see all the CRUD operations are reverted back when performed inside begin tran and rollback tran, to under stand your stored proc give the code.

begin tran
create table test (a int)
insert into test values(1)
truncate table test
select * from test
rollback tran
select * from test

Kapil Arya

kapilarya
Yak Posting Veteran

86 Posts

Posted - 2005-08-28 : 03:20:47

CRUD ( Create Retreive, Update and Delete)

Kapil Arya

Kristen
Test

22859 Posts

Posted - 2005-08-28 : 05:30:23

Ah - ISUD !! (Insert Select Update Delete

)

Thanks - CRUD doesn't fit well with my usage of the English language

quote:

http://dictionary.reference.com/search?q=crud
Slang.
1a. A coating or an incrustation of filth or refuse.
1b. Something loathsome, despicable, or worthless.
1c. One who is contemptible or disgusting.
2. A disease or ailment, imaginary or real, especially one affecting the skin.
3. Sports. Heavy, sticky snow that is unsuitable for skiing.

Kristen

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2005-08-28 : 11:13:23

I guess I've been in the business too long, Kristen. I think your definitions fit perfectly for how much I care about the data I'm responsible for creating/maintaining CRUD/ISUD operations for

dve,
>>Is it possible to perform some CRUD operations in a stored procedure outside of the context of the transaction it is called in?

No, all CUD operations (even ones in called SPs) will be rolled back when the containing transaction is rolled back.
I intentionally removed the R (that's S to you, Kristen) because READs can't be rolled back. :)

btw, don't be afraid to perform little tests to see for yourself. That's what I usually do just to make sure I'm not stepping in any crud:


set nocount on
create table crud (i int)
go
create proc crud_ins @i int as set nocount on insert crud (i) values (@i)
go

begin tran
exec crud_ins @i = 1
select * from crud
rollback tran

go

select * from crud

go
drop proc crud_ins
drop table crud
go

Be One with the Optimizer
TG

Kristen
Test

22859 Posts

Posted - 2005-08-29 : 06:31:43

"mySproc's CRUD operations are not rolled back"

This bit of the original post bothered me!

Kristen

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources