Basic control errors (transaction)

Hi. As I commented before, I'm new with all that and, with your help, I'm
finishing my first SP.
Now I want to wrap it inside a transaction, so if there was any error, make
a rollback. If not, commit the transaction.
How can I do that?
Regards,
Diego F.I tell you what I'm doing:
declare @.errores as bit
begin transaction
set @.errores = 0
...
sql instruction
...
if (@.@.error<>0) set @.errores = 1
...
sql instruction
...
if (@.@.error<>0) set @.errores = 1
if (@.errores=0)
commit transaction
else
rollback transaction
And that's all.
Is that a good way to do it?
Regards,
Diego F.
"Diego F." <diegofrNO@.terra.es> escribi en el mensaje
news:etaEJ5RjFHA.3332@.TK2MSFTNGP10.phx.gbl...
> Hi. As I commented before, I'm new with all that and, with your help, I'm
> finishing my first SP.
> Now I want to wrap it inside a transaction, so if there was any error,
> make a rollback. If not, commit the transaction.
> How can I do that?
> --
> Regards,
> Diego F.
>
>|||Hi,
Looks good. see also SAVE TRANSACTION in books online.
Thanks
Hari
SQL Server MVP
"Diego F." <diegofrNO@.terra.es> wrote in message
news:eRdWaDSjFHA.2904@.tk2msftngp13.phx.gbl...
>I tell you what I'm doing:
> declare @.errores as bit
> begin transaction
> set @.errores = 0
> ...
> sql instruction
> ...
> if (@.@.error<>0) set @.errores = 1
> ...
> sql instruction
> ...
> if (@.@.error<>0) set @.errores = 1
> if (@.errores=0)
> commit transaction
> else
> rollback transaction
> And that's all.
> Is that a good way to do it?
> --
> Regards,
> Diego F.
>
>
> "Diego F." <diegofrNO@.terra.es> escribi en el mensaje
> news:etaEJ5RjFHA.3332@.TK2MSFTNGP10.phx.gbl...
>|||Check this out:
http://msdn.microsoft.com/newsgroup...c7ea&sloc=en-us
ML|||Hi
@.@.ERROR contains the error number which you are loosing by doing it your wa
y.
You may want to read the topic "Using @.@.ERROR" in books online. The
following also has examples and are a 'must read'!
http://www.sommarskog.se/error-handling-I.html and
http://www.sommarskog.se/error-handling-II.html
John
"Diego F." wrote:

> I tell you what I'm doing:
> declare @.errores as bit
> begin transaction
> set @.errores = 0
> ...
> sql instruction
> ...
> if (@.@.error<>0) set @.errores = 1
> ...
> sql instruction
> ...
> if (@.@.error<>0) set @.errores = 1
> if (@.errores=0)
> commit transaction
> else
> rollback transaction
> And that's all.
> Is that a good way to do it?
> --
> Regards,
> Diego F.
>
>
> "Diego F." <diegofrNO@.terra.es> escribió en el mensaje
> news:etaEJ5RjFHA.3332@.TK2MSFTNGP10.phx.gbl...
>
>|||OK, but I don't need the error number by now.
Thanks.
Regards,
Diego F.
"John Bell" <jbellnewsposts@.hotmail.com> escribi en el mensaje
news:F2A2A03F-5B9B-40F6-8169-EA1FB3CE9B62@.microsoft.com...
> Hi
> @.@.ERROR contains the error number which you are loosing by doing it your
> way.
> You may want to read the topic "Using @.@.ERROR" in books online. The
> following also has examples and are a 'must read'!
> http://www.sommarskog.se/error-handling-I.html and
> http://www.sommarskog.se/error-handling-II.html
> John
> "Diego F." wrote:
>|||Hi
It is not a good idea to throw away information that may be important when
trying to work out why/what has gone wrong. There error number may
significantly reduce the time it takes to isolate a particular problem and
should be propogated back to whereever the problem is reported.
John
"Diego F." wrote:

> OK, but I don't need the error number by now.
> Thanks.
> --
> Regards,
> Diego F.
>
> "John Bell" <jbellnewsposts@.hotmail.com> escribió en el mensaje
> news:F2A2A03F-5B9B-40F6-8169-EA1FB3CE9B62@.microsoft.com...
>
>