TSQL - create a stored proc inside a transaction statement

Posted by Chris L on Stack Overflow See other posts from Stack Overflow or by Chris L
Published on 2010-04-26T16:42:38Z Indexed on 2010/04/26 17:13 UTC
Read the original article Hit count: 152

Filed under:

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).

© Stack Overflow or respective owner

Related posts about stored-procedures