Create a procedure in a SQL Server transaction

Question

I am new to SQL Sever and I am trying to create a procedure inside in a transaction as follow:

        BEGIN TRY
        SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
        BEGIN TRANSACTION MyTran

        --*****************************************************************************         
        USE [Database]
        Create PROCEDURE [dbo].[SP_StoreProcedure] AS

        --Some SQL Queries here
        Go

        Commit TRANSACTION MyTran
        END TRY

        BEGIN CATCH
            ROLLBACK TRANSACTION ;
        END CATCH;
        SET TRANSACTION ISOLATION LEVEL READ COMMITTED

However I get these errors:

Msg 156, Level 15, State 1, Line 9
Incorrect syntax near the keyword 'PROCEDURE'.

Msg 102, Level 15, State 1, Line 5
Incorrect syntax near 'BEGIN'.

Msg 102, Level 15, State 1, Line 14
Incorrect syntax near 'END'.

If this can not be done in SQL Server what is a workaround?

Side note: you should **not** use the `sp_` prefix for your stored procedures. Microsoft has [reserved that prefix for its own use (see *Naming Stored Procedures*)](http://msdn.microsoft.com/en-us/library/ms190669%28v=sql.105%29.aspx), and you do run the risk of a name clash sometime in the future. [It's also bad for your stored procedure performance](http://www.sqlperformance.com/2012/10/t-sql-queries/sp_prefix). It's best to just simply avoid `sp_` and use something else as a prefix - or no prefix at all! — marc_s, Nov 14 '14 at 10:06
How to create procedure?? is that your question?? Check this link http://msdn.microsoft.com/en-us/library/ms187926.aspx — Pரதீப், Nov 14 '14 at 10:07
you can't create a procedure _inside_ a transaction - that doesn't even make sense - what are you trying to accomplish? — ninesided, Nov 14 '14 at 10:13
@ninesided I have multiple store procedure to create. I want them all to be create in one transaction. — user1912404, Nov 14 '14 at 12:30
Google how to create sql server stored procedure. It can't be used that way. You should read the documentation first before posting silly question. — Eric, Nov 09 '18 at 22:45

score 2 · Answer 1 · answered Nov 14 '14 at 10:08

2

The creation of the stored procedure should be the first statement in your script. Place your stored procedure creation in a VARCHAR(MAX) (ie as a string), and use EXEC sp_executesql <your_sp_creation_varchar>

answered Nov 14 '14 at 10:08

TT.

15,774
6
47
88

Mind that my answer is really for when the OP intended to create the Stored Procedure within a transaction (as he claims, he might not have written it properly). Not if the OP intended to use a transaction in the SP. If the latter is true, please refer to the other answers on this page. – TT. Nov 14 '14 at 10:13
Thank you for your answer. I acknowledged the solution however I am facing another problem now. I have multiple procedure to create and I need to create them all in one transaction. Is there another solution beside putting them in a varchar since the sp body is quite long and have lots of single quotes. – user1912404 Nov 14 '14 at 12:01
No. TT's solution is correct and is the way that a few tools script large deployments within a transaction. – Bruce Dunwiddie Nov 14 '14 at 20:31
@user1912404 There is no other way to do this but to place them in VARCHAR's and use the method I outlined. Yes, escaping the single quotes is a drag... – TT. Nov 15 '14 at 09:40

score 0 · Answer 2 · answered Nov 14 '14 at 10:06

Your Syntax is Wrong

    Create PROCEDURE [dbo].[SP_StoreProcedure] AS
     SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
     BEGIN
       BEGIN TRY
       BEGIN TRANSACTION MyTran

    --Some SQL Queries here


       Commit TRANSACTION MyTran
       END TRY

       BEGIN CATCH
        ROLLBACK TRANSACTION ;
        PRINT ERROR_MESSAGE();   -- For Debugging
      END CATCH;
      SET TRANSACTION ISOLATION LEVEL READ COMMITTED
    END

score 0 · Answer 3 · answered Nov 14 '14 at 10:10

    USE [Database]
    Create PROCEDURE [dbo].[SP_StoreProcedure] AS
    SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
    BEGIN TRANSACTION MyTran
    BEGIN TRY

    Commit TRANSACTION MyTran
    END TRY

    BEGIN CATCH
        ROLLBACK TRANSACTION MyTran;
    END CATCH;

MBentley · Answer 4 · 2018-11-09T22:36:22.930

There is a good answer in TSQL - create a stored proc inside a transaction statement.

user KM says: 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

Create a procedure in a SQL Server transaction

4 Answers4