Using transactions and output parameters

Question

Post reply

Using transactions and output parameters

Mithrandir

SSCrazy

Points: 2628
More actions
March 12, 2004 at 10:45 am

#107785
Hi.
I'm trying to create a SP using transactions to call him from ASP, but I dont want to manage OnError ASP code. I want to check error status via T-SQL Output variables.
Supposing I have some like this:
```
create procedure MyProc(@result int output)
as
begin transaction
insert into MyTable values(1,2,3)
if @@error
begin
@result = -1
rollback tran
end
else
begin
@result = 0
commnt tran
end
```
I try the SP in QA (
```
declare @status int exec MyProc @status output print @status
```
) and get this results:
1) When operation success I get a 0 result, all fine.
2) When operation fails I still get a 0 result!!! (I supposed a -1 should be returned), and error msg is displayed.
¿Can I do what I want? ¿How?
¿What other methods suggest me?
Thanks

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

Steve Henry-128072 SSC Veteran Points: 261 More actions · Answer 1

First glance looks like your IF statement; @@Error is not boolean. Change it to: IF @@Error <> 0...

I use this method, sometimes passing back the actual error number, sometimes a constant (like you do with -1).

Steve

Mithrandir SSCrazy Points: 2628 More actions · Answer 2

Sorry, was a typo error, I actually have

....
if @@error  0 
begin
@result = -1
rollback tran
....

But doesn't work, is the behavior described above

Jack Corbett SSC Guru Points: 184393 More actions · Answer 3

My guess is that you should have:

rollback tran

Set @result = -1

In that order. I did not see the Set so I do not know how oyou were successfully creating the proc. A rollback does not exit a procedure so I think that is how you are still getting the 0.

Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question