Rollback is not happening at all...why?

I have the below

Declare @tbl Table(Id int identity, SomeCol varchar(10) not null)

Begin Transaction Tran1
   Declare @ErrorNum int  
   Declare @i int   
   Set @i =1

   --Start Operation
   While(@i <= 10)
    Begin

        If(@i = 9)
            Begin
                Insert into @tbl(SomeCol) Values(null)
                Set @ErrorNum = @@ERROR
            End
        Else
            -- All records will be inserted successfully
            Begin
                Insert into @tbl(SomeCol) Values(@i)
            End
        Set @i = @i +1

    End -- End of while

    -- If there is any error, notify that and roll back the transaction
   IF @ErrorNum <> 0 
   BEGIN 
      RAISERROR ('Attempt to insert null value in [Phone Number] is not allowed',16,1) 
      Rollback Transaction Tran1
   End
IF (@ErrorNum = 0)
   COMMIT TRANSACTION Tran1

   select * from @tbl

What I am trying to do is that, if the value of @i is 9 , I am trying to insert a null value to the @tbl which should not allow at all and should rollback all the records and will generate only the custom exception.

But it is giving both system and custom exception and the records have been inserted and not rolledback except for the 9th record.

The below is what I got in the Message Tab

**(1 row(s) affected)
(1 row(s) affected)
(1 row(s) affected)
(1 row(s) affected)
(1 row(s) affected)
(1 row(s) affected)
(1 row(s) affected)
(1 row(s) affected)
Msg 515, Level 16, State 2, Line 14
Cannot insert the value NULL into column 'SomeCol', table '@tbl'; column does not allo开发者_Go百科w nulls. INSERT fails.
The statement has been terminated.
(1 row(s) affected)
Msg 50000, Level 16, State 1, Line 29
Attempt to insert null value in SomeCol is not allowed
(9 row(s) affected)**

And the below is in the Records tab

Id  SomeCol
1   1
2   2
3   3
4   4
5   5
6   6
7   7
8   8
10  10

I am not sure what wrong I have done.

Help needed.

Table variables don't get rolled back. Try using a #temporary table instead and your script should work as expected!

create  Table #tbl(Id int identity, SomeCol varchar(10) not null)

Begin Transaction Tran1
   Declare @ErrorNum int  
   Declare @i int   
   Set @i =1

   --Start Operation
   While(@i <= 10)
    Begin

        If(@i = 9)
            Begin
                Insert into #tbl(SomeCol) Values(null)
                Set @ErrorNum = @@ERROR
            End
        Else
            -- All records will be inserted successfully
            Begin
                Insert into #tbl(SomeCol) Values(@i)
            End
        Set @i = @i +1

    End -- End of while

    -- If there is any error, notify that and roll back the transaction
   IF @ErrorNum <> 0 
   BEGIN 
      RAISERROR ('Attempt to insert null value in [Phone Number] is not allowed',16,1) 
      Rollback Transaction Tran1
   End
IF (@ErrorNum = 0)
BEGIN 
   PRINT 'COMMIT'
   COMMIT TRANSACTION Tran1
END
   select * from #tbl

drop table #tbl

It looks like that you are never get to Rollback Transaction Tran1 - since RAISE appears before. Just swap both lines

Table variables cannot be rollbacked. See here.

If you add the following to the begining of your script then the entire transaction will be rolled back on failure (no partially loaded table variable), but you might not see your custom error message:

SET XACT_ABORT ON 

From the Microsoft documentation:

When SET XACT_ABORT is OFF, in some cases only the Transact-SQL statement that raised the error is rolled back and the transaction continues processing. Depending upon the severity of the error, the entire transaction may be rolled back even when SET XACT_ABORT is OFF. OFF is the default setting.

If you'd like the transaction to be rolledback and to issue your custom error messasge, you could use Try...Catch:

BEGIN TRY
    Declare @tbl Table(Id int identity, SomeCol varchar(10) not null)

    Begin Transaction Tran1
       Declare @i int   
       Set @i =1

       --Start Operation
       While(@i <= 10)
        Begin

            If(@i = 9)
                Begin
                    Insert into @tbl(SomeCol) Values(null)
                End
            Else
                -- All records will be inserted successfully
                Begin
                    Insert into @tbl(SomeCol) Values(@i)
                End
            Set @i = @i +1

        End -- End of while

        COMMIT TRANSACTION Tran1

       select * from @tbl
END TRY
BEGIN CATCH
       BEGIN 
          RAISERROR ('Attempt to insert null value in [Phone Number] is not allowed',16,1) 
          Rollback Transaction Tran1
       End
END CATCH