Posted by Erland Sommarskog on 12/06/06 22:53

Matthew Wells (MWells@FirstByte.net) writes:
> That's not exactly true...
> I can fire
>
> Begin Tran
>
> Statement 1
> Statement 2
> Statement 3
> GO
>
> -- I want to test here if the previos block was rolled back, but
> --all variables declared earler lose scope
> Statement 1
> Statement 2
> Statement 3
> GO
>
> Statement 1
> Statement 2
> Statement 3
> GO
>
> Rollback Tran
>
> and all statements are rolled back. I want to test after each block to
> see if an error occurred so I can skip the rest of the script. Is there
> a way to declare a variable whose scope is the entire script?

No, there isn't. As I said, each batch is sent to SQL Server
independently. You would need variables on the client side, but that
is not offered by Query Analyzer. Whence my recommendation to make
an install script in a language like Perl or VBscript, where you have
better control in Query Analyzer.

What you could do is to all batches this:

IF @@tracount > 0
BEGIN
Statement1
Statement2
Statement3
END

Yet an alternative is to have all in one batch, and if there are
statement that will not compile because previous statements have
not yet been executed, you could put these in EXEC() or sp_executesql.

--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

[Back to original message]