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

I decided to pull the create proc out of the transaction & try/catch into it's own section. I put in an "if exists then drop" statement and added an "if exists then print message" statments(s) around the stored proc creation. Thanks for all the feedback, I learned something from it. — Chris L, Apr 26 '10 at 18:47

score 15 · Answer 1 · answered Apr 26 '10 at 16:46

15

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

answered Apr 26 '10 at 16:46

KM.

101,727
34
178
212

4

+1. This has to be done, as the `create procedure...` call has to be the first statement in the script. Putting it into its own batch with `EXEC` solves this. This can also be used to create script files that either create or update a stored procedure. Very useful for procedure script archiving. – Adam Robinson Apr 26 '10 at 17:00
In the sample, if you run it directly(put in a real table name for some_table) with the ddl/dml stuff commented out the error still happens, and yet the create procedure is the first statement, or am I misunderstanding. – Chris L Apr 26 '10 at 17:08
the code runs fine for me, I'm not sure what you are changing – KM. Feb 11 '11 at 13:53
KM, you know about the issues that user1035920 just pointed out below? – knocte Nov 08 '11 at 15:46
@knocte, you could always follow your EXEC() with a check of the system tables to see if the procedure exists and if not, issue a RAISERROR() to fall into the CATCH and rollback. – KM. Nov 08 '11 at 19:29

Thomas · Answer 2 · 2014-09-01T19:23:23.123

12

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( [Error] 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

edited Sep 01 '14 at 19:23

answered Apr 26 '10 at 17:10

Thomas

63,911
12
95
141

SSMS complains that `Error` is not a column in `#tmpErrors (Error) Select 1` should it be `ID` (or `Create Table #tmpErrors( Id int not null )` should be `Create Table #tmpErrors( Error int not null )`? – Sam Holder Sep 01 '14 at 15:52
@SamHolder - Yes. That's a typo on my part that I corrected. If you go with `Id`, then the Create Table statement should obviously use `Id`. If you go with `Error`, then the Create Table statement needs to be updated for that fact. – Thomas Sep 01 '14 at 19:23
this is an EXCELLENT answer indeed thank you very much. where did you learn this? – Alex Gordon Feb 04 '16 at 03:08
@PleaseStopUpvotingMe - As mentioned in my post, this is how Red-Gate's SQL Compare scripts its data changes. – Thomas Feb 05 '16 at 17:13

score 2 · Answer 3 · answered Nov 08 '11 at 15:42

There are various issues with the solution proposed by KM:

If the content of what you put in your EXEC() call is incorrect semantically (for example you put a non-existant table in the FROM inside the stored procedure) that error doesn't bubble up and the transaction is not rolled back.
If the content of what you put in your EXEC() call is incorrect syntactically (for example you put SELECTT instead of SELECT inside the stored procedure), the transaction seems to be rolled back but a completely cryptic error bubbles up:
```
A transport-level error has occurred when receiving results from the server. (provider: TCP Provider, error: 0 - The specified network name is no longer available.)
```

So I'm still at a loss to how to create a procedure inside a transaction but still have the transaction and the try-catch be useful.

Use the CATCH block that is in Mile's answer here: http://stackoverflow.com/questions/180075/executing-a-stored-procedure-inside-begin-end-transaction — knocte, Nov 08 '11 at 16:44

score 0 · Answer 4 · answered Apr 26 '10 at 16:46

0

I seem to recall you cannot do things like create, modify or drop database schema objects—including stored procedures—inside a transaction (since such structural changes are not transactional: you cannot roll them back with data changes).

answered Apr 26 '10 at 16:46

Richard

106,783
21
203
265

1

I tested out the code from my answer, it not only creates a stored procedure, but if you force a ROLLBACK in the code, it will roll it back as well. – KM. Apr 26 '10 at 16:53
1

@KM: This could have changed in more recent versions of SQL Server of course. – Richard Apr 26 '10 at 18:42

TSQL - create a stored proc inside a transaction statement

4 Answers4

Linked