Rollback Trans ?

PhilipL wrote:
> I have a sp with 2 insert statements followed by 1 delete statement.
> How do I set up the Rollback Trans code if an error occurs so the
> statements are not commited. Any help or link is appreciated.
> Thanks
> PhilipL

SQL Server or Oracle?

--
Colin McGuigan

for SQL Server: check out:
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnsqlpro2k/
html/sql00f15.asp
http://builder.com.com/article.jhtml?id=u00320020705adm01.htm
http://www.sqlteam.com/item.asp?ItemID=2290
--
HTH,
David Satz
Principal Web Engineer
Hyperion Solutions

"PhilipL" <aplatfl@aol.com> wrote in message
news:3e1db706$1@tnews.web.dev-archive.com...
>
> I have a sp with 2 insert statements followed by 1 delete statement. How
do
> I set up the Rollback Trans code if an error occurs so the statements are
> not commited. Any help or link is appreciated.
> Thanks
> PhilipL

"Colin McGuigan" <cmcguigan@imany.com> wrote:
>PhilpL wrote:
>> SQL Server
>
>DECLARE @Error int
>
>BEGIN TRANS
>
>INSERT INTO MyTable ...
>
>SET @Error = @@ERROR
>IF @Error <> 0 GOTO ErrorBin
>
>INSERT INTO MyTable ...
>
>SET @Error = @@ERROR
>IF @Error <> 0 GOTO ErrorBin
>
>DELETE MyTable ...
>
>SET @Error = @@ERROR
>IF @Error <> 0 GOTO ErrorBin
>
>COMMIT TRANS
>
>RETURN 0
>
>ErrorBin:
>
>ROLLBACK TRANS
><Error logging code goes here>
>RETURN @Error
>
>------
>
>The reason for SET @Error = @@ERROR is that @@ERROR gets cleared after
>any successful statement, so once you do the GOTO ErrorBin, @@ERROR
>would be reset to 0.
>
>--
>Colin McGuigan

Thanks a lot.
PhilipL