How to fix Error 11722 on Windows Server 2025 / Windows 11

by

Troubleshooting Guide: Error 11722 – “NEXT VALUE FOR function is not allowed in the WHEN MATCHED clause”

Why This Error Happens

This error occurs because SQL Server explicitly restricts the use of the NEXT VALUE FOR sequence function inside the WHEN MATCHED clause of a MERGE statement, as this could cause non-deterministic sequence consumption during row-matching evaluation. The restriction exists to prevent the sequence generator from advancing unpredictably when SQL Server internally re-evaluates matched rows. This is a T-SQL syntax/design limitation rather than a permissions, port, or corruption issue, meaning the query logic itself must be restructured rather than “repaired.”

Step-by-Step Resolution Guide

Step 1: Identify the Offending MERGE Statement

  • Open SQL Server Management Studio (SSMS) on your Windows Server 2025 / Windows 11 instance and locate the stored procedure, trigger, or ad-hoc script that triggered Error 11722.
  • Search specifically for NEXT VALUE FOR [SequenceName] text occurring within the WHEN MATCHED THEN UPDATE SET block of a MERGE statement.
  • Note the column(s) being assigned the sequence value for use in the next step.

Step 2: Move Sequence Generation Outside the MERGE Statement

  • Declare a table variable or temporary table (e.g., DECLARE @StagingData TABLE (...)) prior to the MERGE call.
  • Populate this staging table first, generating the sequence value using SELECT NEXT VALUE FOR [SequenceName] AS NewSeqVal, * FROM [SourceTable].
  • Rewrite the MERGE statement to reference the pre-computed NewSeqVal column from the staging table instead of calling NEXT VALUE FOR directly inside WHEN MATCHED.
  • Example pattern:
  MERGE TargetTable AS T
  USING @StagingData AS S
  ON T.ID = S.ID
  WHEN MATCHED THEN
      UPDATE SET T.SeqColumn = S.NewSeqVal;

Step 3: Validate Sequence Object Permissions and Scope

  • Confirm the executing login has UPDATE permission on the sequence object: GRANT UPDATE ON OBJECT::dbo.YourSequence TO [YourUser];
  • Check that the sequence isn’t being called redundantly in both WHEN NOT MATCHED and the staging query, which can cause gaps or double-increments.
  • Review Dynamic Management View sys.sequences to confirm current value and increment settings align with expected business logic.

Step 4: Deploy and Test the Corrected Script

  • Save the revised stored procedure or script.
  • Execute the script in a non-production/staging instance of SQL Server 2025 first to confirm Error 11722 no longer appears.
  • Check SQL Server Error Logs (via EXEC sp_readerrorlog or Event Viewer under Applications and Services Logs > MSSQLSERVER) to confirm no residual errors are logged.

Alternative or Verification Steps

  • Verification: Run the corrected MERGE statement against a test dataset containing duplicate/matching keys and confirm the sequence column increments exactly once per row without throwing Error 11722.
  • Alternative: If restructuring the MERGE is not feasible, replace the MERGE statement entirely with separate UPDATE and INSERT statements, applying NEXT VALUE FOR directly within the standalone UPDATE statement’s SET clause, since this restriction only applies within MERGE syntax.