Post reply

Handling error in t-Sql script DURING Create Procedure

  • June 15, 2011 at 9:33 am

    #252833

    Hello all,

    I have a large t-sql script that is implementing many database changes and il be run at multiple customer sites. In this script, every change should occur or none of the changes should occur. So, the script starts with BEGIN TRANSACTION and ends with COMMIT TRANSACION. In between, there are multiple batches. Within each batch is a TRY-CATCH block. Within the TRY block are 1 or more SQL statements that perform the changes. If errors are caught in a CATCH block, the transaction is rolled back, an error is raised and I return from the batch. I have ":On Error Exit" as the first line of the script and I use sqlcmd to run the script. So, raising the error will force the script to exit immediately and no further batches execute. Since I will have rolled back the transaction, any changes will have been dumped. This all seems to be working quite nicely when detecting errors during the script as well as when no errors get detected and the script runs to completion.

    However, I have hit a snag when it comes to creating a stored procedure in the script. Creating a stored procedure has to be the only statement within a batch so there is no TRY-CATCH block surrounding it. How would I detect an error WHILE creating the stored procedure and then roll back the transaction?

    Any ideas are welcome.

    Thanks,

    Chuck Greely

  • June 15, 2011 at 9:37 am

    #1339089

    i think you'll have to switch to dynamic sql instead for stored procs,views and functions...

    edit: just plain bad advice/not needed. see opc.three's excellent comment below.

    Lowell

    --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

  • June 15, 2011 at 9:54 am

    #1339099

    Thank you Lowell..I will investigate.

  • June 15, 2011 at 1:58 pm

    #1339301

    You don't need to go down the dynamic SQL route unless you have another requirement not stated in your post regarding the use of TRY/CATCH. If you construct your sql script like this:

    SET XACT_ABORT ON

    GO

    BEGIN TRAN

    GO

    -- batch 1 maybe with some updates, inserts or deletes

    GO

    -- batch 2 maybe contains a create table stmt

    GO

    -- batch 3 maybe contains a create proc stmt

    GO

    -- batch 4 whatever...

    GO

    COMMIT TRAN

    GO

    ...and provide the -b switch when executing the script with SqlCmd you can do away with the use of TRY/CATCH.

    There are no special teachers of virtue, because virtue is taught by the whole community.
    --Plato

Viewing 4 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply