Newbie: Transaction Commits

[Stedo]

hi,

I am writing some stored procedures in T-SQL, the contain both selects and updates. I want to make these transaction safe.

I place a TRANSACTION BEGIN at the start of the procedure, however I use RETURN at various points to exit the procedure based on different conditions.

Where do I place the COMMIT statement? Before each RETURN? Or just at the bottom of the stored procedure?

Thanks in advance
Steve

 

[hmckillop]

it should be like below

CREATE proc usp_tester
as
Begin
	
	declare @v int
	begin tran
	if @v = 1
	begin
		commit tran
		return
	end
	
	commit tran

end

the commit or rollback tran should before the return

"I'm living so far beyond my income that we may almost be said to be living apart

 

[vongrunt]

Or make code more pass-through & structural - one entry point, one exit point:

CREATE proc usp_tester
as
begin
    declare @v int; set @v = 0
    
    begin tran
    
    if @v <> 1
    begin
    		do something, set @v to 1 if you want to exit
    end

    if @v <> 1
    begin
    		do something #2, same thing
    end
    
    commit tran
end

------
"There's a man... He's bald and wears a short-sleeved shirt, and somehow he's very important to me. I think his name is Homer."
(Jack O'Neill, Stargate)

 

[Stedo]

hi,

Thanks for your help. How do I know when the transaction is being rolled back? I'm called these stored procedures from C#, is an exception thrown?

Thanks
Steve

 

[hmckillop]

If you need to rollback errors you can do specific statements
i.e.

IF @@ERROR > 0 
BEGIN
 Rollback Tran
 RETURN 123 --replace this with an error code
END

--this will return as a aparameter in C# and then you can capture the error code.
Alternatively use the RAISERROR command in SQL to rais an error and then use try catch.


"I'm living so far beyond my income that we may almost be said to be living apart