Since the case statement is doing incremental steps, it can be simplified using integer division, see the example.

😎

USE tempdb;

GO

SET NOCOUNT ON;

DECLARE @SET_SIZE INT = 40000;

/* Test data 1 to @SET_SIZE */

;WITH T(N) AS (SELECT N FROM (VALUES (NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL)) AS X(N))

,NUMS(N) AS (SELECT TOP(@SET_SIZE) ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) AS N FROM T T1, T T2, T T3, T T4, T T5, T T6, T T7)

SELECT

NM.N AS BATCH

-- UNCOMMEMNT THE FOLLOWING LINES TO ASSERT THE LOGIC

--,FLOOR(NM.N / 1000) AS GroupNo

--,CHAR(FLOOR(NM.N / 1000) + 55) AS GroupChar

,CASE

WHEN NM.N BETWEEN 10000 AND 35999 THEN CHAR(FLOOR(NM.N / 1000) + 55) + RIGHT(CAST(NM.N AS VARCHAR(12)),3)

WHEN NM.N < 10000 THEN CAST(NM.N AS VARCHAR(4))

ELSE ''

END AS [DDP_BATCH_#]

FROM NUMS NM

WHERE NM.N > 9990;