Begin Tran / Commit Tran

Any reason why you would need to wrap a transaction around a single update?
BEGIN TRANSACTION
UPDATE ThisTable
SET blah blah blah
WHERE foo = @.bar
COMMIT TRANSACTION
?!?
Peace & happy computing,
Mike Labosh, MCSD
"Musha ring dum a doo dum a da!" -- James HetfieldNo need. The only reason I can think of if you were to name the transaction.
However, naming
transaction has little benefit.
I think you can see the transaction names as they are rolled forward and rol
led back during
automatic recovery in the errorlog (at least you used to be able to do that)
. That is about the only
reason I can think of. Without a name = waste of precious keyboard keys.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Mike Labosh" <mlabosh@.hotmail.com> wrote in message news:eNrwSe0pFHA.1024@.TK2MSFTNGP09.phx
.gbl...
> Any reason why you would need to wrap a transaction around a single update
?
> BEGIN TRANSACTION
> UPDATE ThisTable
> SET blah blah blah
> WHERE foo = @.bar
> COMMIT TRANSACTION
> ?!?
> --
> Peace & happy computing,
> Mike Labosh, MCSD
> "Musha ring dum a doo dum a da!" -- James Hetfield
>|||> No need. The only reason I can think of if you were to name the
> transaction. However, naming transaction has little benefit.
> I think you can see the transaction names as they are rolled forward and
> rolled back during automatic recovery in the errorlog (at least you used
> to be able to do that). That is about the only reason I can think of.
> Without a name = waste of precious keyboard keys.
Yeah, ok, that's what I thought. Thanks for the much needed "sanity-check".
--
Peace & happy computing,
Mike Labosh, MCSD
"Musha ring dum a doo dum a da!" -- James Hetfield|||If that's all that's there in the proc, then it's a waste of keystrokes.
Otherwise, if it ain't broke, don't fix it. The real question is, where's
the error handling and the ROLLBACK? someone may have modified another
working procedure that updated multiple tables and just changed the names to
protect the innocent. It won't hurt anything to leave it in, and if you
ever need to add an update to another table, you've already got all of the
error handling and cleanup in place.
You may have a structure something like:
CREATE PROCEDURE procName ...
BEGIN
..
SET @._TRANCOUNT = @.@.TRANCOUNT
IF @._TRANCOUNT > 0
SAVE TRANSACTION transactionName
ELSE
BEGIN TRANSACTION transactionName
..
IF @._TRANCOUNT = 0 -- only commit if new tran started
COMMIT TRANSACTION transactionName
RETURN 0 -- indicate success
ERROR:
IF @._TRANCOUNT IS NOT NULL -- only roll back if necessary
IF @.@.TRANCOUNT > 0 -- roll back any changes made by this proc
ROLLBACK TRANSACTION transactionName
IF @.RC > 0 RETURN @.RC -- return error from called sp
IF @._ERROR > 0 RETURN @._ERROR -- return error raised in this sp
RETURN -1 -- indicate failure
END
"Mike Labosh" <mlabosh@.hotmail.com> wrote in message
news:eNrwSe0pFHA.1024@.TK2MSFTNGP09.phx.gbl...
> Any reason why you would need to wrap a transaction around a single
update?
> BEGIN TRANSACTION
> UPDATE ThisTable
> SET blah blah blah
> WHERE foo = @.bar
> COMMIT TRANSACTION
> ?!?
> --
> Peace & happy computing,
> Mike Labosh, MCSD
> "Musha ring dum a doo dum a da!" -- James Hetfield
>