Batchwise Script Execution

Question

I have long script which contains the Create tables, create schemas,insert data,update tables etc.I have to do this by only on script in batch wise.I ran it before but it created every time some error due to this some object will present inside the database. So In need some mechanism which can handle the batch execution if something goes wrong the whole script should be rolled back.

Appreciated Help and Time.

--343

Answer 1

Try this:

DECLARE @outer_tran int;  
SELECT @outer_tran = @@TRANCOUNT;

-- find out whether we are inside the outer transaction
-- if yes - creating save point if no starting own transaction
IF @outer_tran > 0 SAVE TRAN save_point ELSE BEGIN TRAN;

BEGIN TRY
    -- YOUR CODE HERE

    -- if no errors and we have started own transaction - commit it
    IF @outer_tran = 0 COMMIT;
END TRY
BEGIN CATCH
    -- if error occurred - rollback whole transaction if it is own
    -- or rollback to save point if we are inside the external transaction
    IF @outer_tran > 0 ROLLBACK TRAN save_point ELSE ROLLBACK;

    --and rethrow original exception to see what happens
    DECLARE
        @ErrorMessage nvarchar(max),
        @ErrorSeverity int,
        @ErrorState int;

    SELECT
        @ErrorMessage = ERROR_MESSAGE() + ' Line ' + cast(ERROR_LINE() as nvarchar(5)),
        @ErrorSeverity = ERROR_SEVERITY(), 
        @ErrorState = ERROR_STATE();

    RAISERROR (@ErrorMessage, @ErrorSeverity, @ErrorState);
END CATCH

Answer 2

While I might not have caught all the nuances of your question, I believe XACT_ABORT will deliver the functionality you seek. Simply add a

SET XACT_ABORT ON;

to the beginning of your script.

With the 2005 release of SQL Server, you have access to try/catch blocks in TSQL as well.