TSQL - create a stored proc inside a transaction statement

Question

I have a sql script that is set to roll to production. I've wrapped the various projects into separate transactions. In each of the transactions we created stored procedures. I'm getting error messages

Msg 156, Level 15, State 1, Line 4 Incorrect syntax near the keyword 'procedure'.

I created this example script to illustrate

Begin Try
Begin Transaction 
    -- do a bunch of add/alter tables here
    -- do a bunch of data manipulation/population here

    -- create a stored proc
  create procedure dbo.test
  as
  begin
    select * from some_table
  end
Commit  
End Try
Begin Catch
    Rollback  
    Declare @Msg nvarchar(max)
    Select @Msg=Error_Message();
    RaisError('Error Occured: %s', 20, 101,@Msg) With Log;
End Catch

The error seems to imply that I can't create stored procs inside of transaction, but I'm not finding any docs that say otherwise(maybe google isn't being freindly today).

Answer 1

try doing the create procedure in EXEC('...'), like this:

Begin Try
Begin Transaction 
    -- do a bunch of add/alter tables here
    -- do a bunch of data manipulation/population here

    -- create a stored proc
  EXEC ('create procedure dbo.test
  as
  begin
    select * from some_table
  end')
Commit  
End Try
Begin Catch
    Rollback  
    Declare @Msg nvarchar(max)
    Select @Msg=Error_Message();
    RaisError('Error Occured: %s', 20, 101,@Msg) With Log;
End Catch

GO

Answer 2

You cannot write your script in this way as there are many statements that must be run in their own batch. Instead, I would recommend doing something akin to how Red-Gate's SQL Compare builds its scripts:

Set Xact_Abort On
GO
Begin Transaction 
GO
If object_id('tempdb..#tmpErrors') is not null
    Drop Table #tmpErrors
GO
Create Table #tmpErrors( Id int not null )
GO
Create Procedure dbo.Test
As
Begin
    --....
End
GO
If @@Error <> 0 AND @@TranCount >0 Rollback Transaction
GO
IF @@TranCount = 0 Begin Insert #tmpErrors (Error) Select 1 Begin Transaction End
GO

-- more statements

GO
If @@Error <> 0 AND @@TranCount >0 Rollback Transaction
GO
IF @@TranCount = 0 Begin Insert #tmpErrors (Error) Select 1 Begin Transaction End
GO

--.....

IF NOT EXISTS(SELECT * FROM #tmpErrors)
    BEGIN
        PRINT 'The database update succeeded'
        IF @@TRANCOUNT > 0 COMMIT TRANSACTION
    END
ELSE 
    BEGIN
        PRINT 'The database update failed'
        IF @@TRANCOUNT > 0 ROLLBACK TRANSACTION
    END
GO

DROP TABLE #tmpErrors
GO