SQL Server Best Practice

[MastermindSQL]

What is the difference between the following two cases:

Case 1:

CREATE PROC MyProc
AS
.....
RETURN
GO
----------
BEGIN TRAN
EXEC MyProc
COMMIT

===================
Case 2:

CREATE PROC MyProc
AS
BEGIN TRAN
.....
COMMIT
RETURN
GO
----------
EXEC MyProc

Which one of the above is a good practice and how the locks are handled (if there is any difference) in each case.

Thanks in advance.

 

[vongrunt]

Case 2 probably. It will still behave transactional even if you forget to wrap EXEC into transaction. Plus BEGIN/END TRAN get compiled that way.

------
"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)