SQL Server 2005 inserting null when value hard coded

Question

I have a stored proc that creates an entery in a table.

INSERT INTO tbl_CaseDetails
        (
            Case_Id
            , Client_Id
            , Client_HO_Id
            , Client_Case_Ref
            , Client_Cust_Ref
            , Access_Id
            , Product_Id
            , Status
        )
        Values(

            @CaseId
            , @Client_Id
            , isnull(@Head_Office_Id,@Client_Id)
            , @Client_Case_Reference
            , @Client_Customer_Reference
            , @Access_Id
            , @Product_Id 
            , 'pre-instr')

But in the trigger the status is appearing as null and the insert fails as i have a check for a null status.

  If Update(Status)
        Begin
              If @Status Is Null
        Exec [usp_CaseWithNullStatus] @Case_Id
                 Begin
                       RaisError ( 'Cannot insert null status'
              ,16
              ,16 )
                 End
        End

Any ideas why this is happening? Sp

score 2 · Answer 1 · answered Feb 25 '11 at 12:18

Shouldn't your inner begin / end block be set this way:

  If Update(Status)
  Begin
      If @Status Is Null
      Begin   -- <---------- block starts here
           Exec [usp_CaseWithNullStatus] @Case_Id
           RaisError ( 'Cannot insert null status'
              ,16
              ,16 )
      End
  End

In your code the raiserror will be always called, because the line following your if statement is considered as execution block and the next line will be called even if the if-statement isn't true.

SQL Server 2005 inserting null when value hard coded

1 Answers1