T-SQL 2005: Combining Multiple Create / Change Procedure Calls in One Transaction

Question

I want to create a T-SQL script change that rolls back from database changes from dev to test to production. I divided the script into three parts:

• DDL statements
• changes to stored procedures (creating and modifying a procedure)
• data creation and modification

I want all changes to these three scenarios to be implemented in a transaction. Either all changes to the script are processed, or, on error, all changes are rolled back. I managed to do this for steps 1 and 3 using the try / catch and begin transaction commands. My problem now is to do the same for stored procedures. The call to "start a transaction" immediately before the "create stored procedure" operator results in a syntax error telling me that "the create / create procedure statement must be the first statement inside the request package." Therefore, I am wondering how I could combine several operators of the create / modify procedure in one transaction.

Any help is appreciated -)

thank

Answer 1

SQL .

EXEC ('CREATE PROC dbo.foo AS ....`)

. " alter/create procedure "

Answer 2

:

begin transaction
    go
    create procedure foo as begin select 1 end
    go
commit transaction

Answer 3

BEGIN TRANSACTION
BEGIN TRY 
    -- Do your stuff here 
    COMMIT TRANSACTION

    PRINT 'Successfull.'

END TRY
BEGIN CATCH

    SELECT 
        ERROR_NUMBER() as ErrorNumber,
        ERROR_MESSAGE() as ErrorMessage;

    ROLLBACK TRANSACTION

END CATCH

Answer 4

try to complete the steps in the task