SQL存储过程其他操作正常，仅INSERT语句无法插入数据，请求排查

Let’s walk through the most likely culprits for those two INSERT statements failing only when @Status='V'—since your UPDATE/DELETE operations work fine, the issue is probably isolated to this branch of logic or the specific values being passed here.

Common Causes & Fixes #

1. Verify the @Status='V' Branch is Actually Executing #

First, double-check that your code is even reaching the INSERT statements when @Status='V'. It’s easy to make a mistake with conditional logic:

• Did you misspell 'V' (e.g., lowercase 'v' or a typo like 'U')?
• Is the INSERT block properly nested inside the IF @Status='V' condition? Missing or misplaced parentheses can cause code to run outside the intended branch.
• Add a debug print at the start of the branch to confirm:

  IF @Status='V'
  BEGIN
      PRINT 'Entered @Status=''V'' branch'
      -- Your existing INSERT statements here
  END
  

2. Check for Invalid or NULL Variable Values #

The INSERT statements depend on several variables—if any of these are invalid (e.g., NULL for a non-nullable column), the insert will fail silently if you don’t have error handling:

• @Receipt_Id, @Role_Id, @User_Id, and @Analyst_Id: Are all of these populated when @Status='V'? For example, @Analyst_Id might be NULL in this scenario, and the User_Id column in BSMS_CPP_RCPT_RETURN_ESIGN doesn’t allow NULLs.
• Add debug output to inspect these values right before the INSERTs:

  DECLARE @Dt DATETIME = GETDATE()
  PRINT '@Receipt_Id: ' + ISNULL(CAST(@Receipt_Id AS VARCHAR), 'NULL')
  PRINT '@Role_Id: ' + ISNULL(CAST(@Role_Id AS VARCHAR), 'NULL')
  PRINT '@User_Id: ' + ISNULL(CAST(@User_Id AS VARCHAR), 'NULL')
  PRINT '@Analyst_Id: ' + ISNULL(CAST(@Analyst_Id AS VARCHAR), 'NULL')
  PRINT '@Label: ' + ISNULL(@Label, 'NULL')
  

3. Validate Table Constraints #

Your table might have constraints that are being violated only when @Status='V':

• Primary Key/Unique Constraints: If BSMS_CPP_RCPT_RETURN_ESIGN has a unique key (e.g., Receipt_Id + User_Id), the records you’re trying to insert might already exist. Check with:

  SELECT * FROM BSMS_CPP_RCPT_RETURN_ESIGN 
  WHERE Receipt_Id = @Receipt_Id 
    AND User_Id IN (@User_Id, @Analyst_Id)
  

• Foreign Key Constraints: Ensure @Receipt_Id exists in the parent table linked via foreign key, and @Role_Id is a valid value in its reference table.
• Data Type Mismatches: Confirm that variable types match the table’s column types (e.g., is User_Role a VARCHAR but @Role_Id is an INT? That would cause an implicit conversion error).

4. Add Error Handling to Catch Exceptions #

Without error handling, SQL Server will fail silently if the INSERT hits an error (especially if SET NOCOUNT ON is enabled). Wrap your INSERTs in TRY/CATCH blocks to capture the exact error:

DECLARE @Dt DATETIME = GETDATE()

BEGIN TRY
    INSERT INTO BSMS_CPP_RCPT_RETURN_ESIGN (Receipt_Id,User_Role,User_Id,User_Datetime,User_Remarks,Action_Label)
    VALUES (@Receipt_Id,@Role_Id,@User_Id,@Dt,@User_Remarks,@Label)
    PRINT 'First INSERT succeeded: ' + CAST(@@ROWCOUNT AS VARCHAR) + ' row(s) inserted'
END TRY
BEGIN CATCH
    PRINT 'First INSERT failed: ' + ERROR_MESSAGE()
END CATCH

BEGIN TRY
    INSERT INTO BSMS_CPP_RCPT_RETURN_ESIGN (Receipt_Id,User_Role,User_Id,User_Datetime,User_Remarks,Action_Label)
    VALUES (@Receipt_Id,@Role_Id,@Analyst_Id,@Dt,@User_Remarks,'Witnessed By')
    PRINT 'Second INSERT succeeded: ' + CAST(@@ROWCOUNT AS VARCHAR) + ' row(s) inserted'
END TRY
BEGIN CATCH
    PRINT 'Second INSERT failed: ' + ERROR_MESSAGE()
END CATCH

5. Check for Triggers on the Table #

A trigger (like an INSTEAD OF INSERT trigger) on BSMS_CPP_RCPT_RETURN_ESIGN might be overriding or blocking the insert. Temporarily disable the trigger to test:

DISABLE TRIGGER ALL ON BSMS_CPP_RCPT_RETURN_ESIGN
-- Run your stored procedure with @Status='V'
ENABLE TRIGGER ALL ON BSMS_CPP_RCPT_RETURN_ESIGN

If the inserts work when the trigger is disabled, the trigger logic is the issue.

6. Verify Permissions (Less Likely, But Worth Checking) #

While your other operations work, confirm that the account executing the stored procedure has explicit INSERT permissions on BSMS_CPP_RCPT_RETURN_ESIGN. It’s possible the permission was granted via a role that doesn’t apply in the @Status='V' context, but this is rare if other operations succeed.

内容的提问来源于stack exchange，提问作者Jitender Singh