CODEWITHSUNDEEP
sqlsql-servert-sqlstored-proceduressql-server-2012
ayman2000
ayman2000

Reputation: 45

Create SQL Server procedure in a transaction

I need to create two procedures in a SQL Server transaction. If failure, I need to rollback the create(s) and any other executed queries in this transaction. I know the create statement must be the first statement in query batch, but I need to know how handle the transaction with multiple batches.

BEGIN TRANSACTION

CREATE PROCEDURE [dbo].[SP_SP-1]
    @id BIGINT
AS
BEGIN
    SET NOCOUNT ON;

    -- SQL statements 
END
GO

CREATE PROCEDURE [dbo].[SP_SP-2]
    @id BIGINT  
AS  
BEGIN
    SET NOCOUNT ON;

    -- SP-2 statements
END
GO

UPDATE Table 
SET Value = '1.0.0.5' 

COMMIT TRANSACTION / ROLLBACK TRANSACTION

Upvotes: 2

Views: 2718

Answers (4)

Dan Guzman
Dan Guzman

Reputation: 46203

Below is one method to execute multiple batches in a transaction. This uses a temp table to indicate if any batch erred and perform a final COMMIT or ROLLLBACK accordingly.

Another method is to encapsulate statements that must be in single-statement batch (CREATE PROCEDURE, CREATE VIEW, etc.) but that can get rather ugly when quotes within the literal text must be escaped.

CREATE TABLE #errors (error varchar(5));
GO

BEGIN TRANSACTION
GO

CREATE PROCEDURE [dbo].[USP_SP-1]
    @id bigint 
AS
BEGIN
    SET NOCOUNT ON;
    -- SP Statments 
END;
GO
IF @@ERROR <> 0 INSERT INTO #errors VALUES('error');
GO

CREATE PROCEDURE [dbo].[USP_SP-2]
     @id BIGINT  
AS  
BEGIN
    SET NOCOUNT ON;
    -- SP-2 Statments
END;
GO
IF @@ERROR <> 0 INSERT INTO #errors VALUES('error');
GO

UPDATE Table SET Value='1.0.0.5' 
GO
IF @@ERROR <> 0 INSERT INTO #errors VALUES('error');
GO

IF EXISTS(SELECT 1 FROM #errors)
BEGIN
    IF @@TRANCOUNT > 0 ROLLBACK;
END
ELSE
BEGIN
    IF @@TRANCOUNT > 0 COMMIT;
END;
GO

IF OBJECT_ID(N'tempdb..#errors', 'U') IS NOT NULL
    DROP TABLE #errors;
GO

Upvotes: 2

ayman2000
ayman2000

Reputation: 45

i find this solution to execute the procedure as string execution , it`s a workaround to execute what i want 

Begin Try
 Begin Transaction
        EXEC ('
                 Create PROCEDURE [dbo].[SP_1]
                     @id bigint 

                         AS
                             BEGIN  
                      SET NOCOUNT ON;
                                    SP-1
                                 END
                         GO
                   Create PROCEDURE [dbo].[SP_Inc_Discovery_RunDoc]
                       @id bigint
                             AS
                       BEGIN    
                              SET NOCOUNT ON;
                                     Sp-2
                              END')
 Update Table set Value='1.0.0.5' 
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

Upvotes: 0

Ajay2707
Ajay2707

Reputation: 5798

To use Transaction, you need to know what is the meaning of transaction. It's meaning of 'Unit of work either in commit state or rollback state'.

So when you use transaction, you must know that where you declare and where you close. So you must start and end transaction in the parent procedure only than it will work as a unit of work i.e. whatever no of query execute of DML statement, it uses the same transaction.

I do not understand why your update statement outside of procedure and transaction portion too.

It should be (See my comments, you can use TRY Catch same as c sharp) :

Create PROCEDURE [dbo].[SP_SP-1]
    @id bigint 
  AS
  BEGIN
   Begin Transaction
     SET NOCOUNT ON;

            -- SP Statments 
            Exec SP_SP-2 @id    --here you can pass the parameter to another procedure, but do not use transaction in another procedure, other wise it will create another transaction
    If @@Error > 0 than
        Rollback 
    Else
        Commit
    End
   END


 GO

 --Do not use transaction in another procedure, otherwise, it will create another transaction which has own rollback and commit and do not participate in the parent transaction
  Create PROCEDURE [dbo].[SP_SP-2]
     @id BIGINT  
    AS  
      BEGIN
      SET NOCOUNT ON;
      -- SP-2 Statments

       END
  GO

Upvotes: 1

Barr J
Barr J

Reputation: 10919

I suggest you to study more about this subject in Handling Transactions in Nested SQL Server Stored Procedures.

From the beginning, your syntax is wrong. You cannot begin a transaction and then create a procedure, you need to do just the opposite: 

CREATE PROCEDURE [dbo].[SP_SP-1]
@id bigint  
AS
BEGIN

   BEGIN TRY

      BEGIN TRANSACTION

           SET NOCOUNT ON;
        -- SP-2 Statments

          Update Table set Value='1.0.0.5'

   END TRY

   BEGIN CATCH 
    --handle error and perform rollback
         ROLLBACK  
      SELECT ERROR_NUMBER() AS ErrorNumber
      SELECT ERROR_MESSAGE() AS ErrorMessage   
   END CATCH
END

It is best practice to use TRY and CATCH when attempting to perform update inside transaction scope.

Please read more and investigate using the link I provided to get a bigger picture.

Upvotes: 2

Related Questions