Люди ранее писала в другой теме.
Была у меня такая процедура:
USE [Om**]
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
--
ALTER PROCEDURE [XAF].[DD_Generate_Number_For_Device_Documents]
AS
BEGIN
SET NOCOUNT, XACT_ABORT, ANSI_PADDING, ANSI_WARNINGS, ARITHABORT, CONCAT_NULL_YIELDS_NULL ON
SET NUMERIC_ROUNDABORT, CURSOR_CLOSE_ON_COMMIT OFF

-- =======================================================================================================================
-- очистка бронированных номеров старше 1 дня
-- =======================================================================================================================
DECLARE @Date datetime;
-- округляем дату до 00:00 и вычитаем 1 день
SELECT @Date = DATEADD(day, -1, CONVERT(varchar(8), GETDATE(), 112));

DELETE XAF.CD_Bookings_Doc_Number
WHERE D_Date < @Date
-- =======================================================================================================================

DECLARE @DocNumbers TABLE(N_Number INT)
DECLARE @NextNumber INT,
@MaxNextNumber INT

-- получаем все используеммые номера за текущий год
INSERT INTO @DocNumbers
SELECT
CASE WHEN (ISNUMERIC(dd.C_Number) = 1 AND dd.C_Number NOT LIKE '%[.,]%') THEN CAST(dd.C_Number AS INT) END -- только коректные номера
FROM dbo.DD_Docs dd
INNER JOIN dbo.DS_Docum_Types ddt
ON ddt.LINK = dd.F_Docum_Types
AND ddt.C_Const IN ('DST_ToleranceFirst', 'DST_ToleranceNext', 'DST_Checkup', 'DST_Return', 'DST_Survey', 'DST_Survey_TES', 'DST_Checkup_TES', 'DST_Return_TES', 'DST_ControlCheckup','DST_AnnulateAct','DST_TU_Giving', 'DST_UU_Matching')
WHERE YEAR(dd.D_Register_Date) = YEAR(GETDATE())

-- максимальный из используеммых и забронированных номеров
SELECT @MaxNextNumber = ISNULL(MAX(t.MaxNumber), 0) + 1
FROM ( SELECT ISNULL(MAX(N_Number), 0) AS MaxNumber FROM @DocNumbers dn
UNION
SELECT ISNULL(MAX(CAST(C_Number AS INT)), 0) FROM XAF.CD_Bookings_Doc_Number
WHERE YEAR(D_Date) = YEAR(GETDATE())
) t

;WITH Numbers(Number) AS
(
SELECT 1
UNION ALL
SELECT Number + 1 FROM Numbers WHERE Number < @MaxNextNumber
)
SELECT TOP 1
@NextNumber = n.Number
FROM Numbers n
LEFT JOIN @DocNumbers dn
ON n.Number = dn.N_Number
LEFT JOIN XAF.CD_Bookings_Doc_Number bdn
ON CAST(bdn.C_Number AS INT) = n.Number
AND YEAR(bdn.D_Date) = YEAR(GETDATE())
WHERE dn.N_Number IS NULL
AND bdn.C_Number IS NULL
OPTION (MAXRECURSION 0)

INSERT INTO XAF.CD_Bookings_Doc_Number
SELECT @NextNumber, GETDATE()

SELECT @NextNumber
END


которая
1.Выбрала первый свободный номер в году (не следующий после максимального, а именно первый свободный, если считать начиная с единицы). При этом занятые резервные номера не брала.
3. Зарезервировала номер в таблице XAF.CD_Bookings_Doc_Number

Задача:
нужно исключить рекурсивный вызов.
Решение:

USE [Om***]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
--
ALTER PROCEDURE [XAF].[DD_Generate_Number_For_Device_Documents]
AS
BEGIN
SET NOCOUNT, XACT_ABORT, ANSI_PADDING, ANSI_WARNINGS, ARITHABORT, CONCAT_NULL_YIELDS_NULL ON
SET NUMERIC_ROUNDABORT, CURSOR_CLOSE_ON_COMMIT OFF

-- =======================================================================================================================
-- очистка бронированных номеров старше 1 дня
-- =======================================================================================================================
DECLARE @date datetime;
-- округляем дату до 00:00 и вычитаем 1 день
SELECT @Date = DATEADD(day, -1, CONVERT(varchar(8), GETDATE(), 112));

DELETE XAF.CD_Bookings_Doc_Number
WHERE D_Date < @Date
-- =======================================================================================================================
DECLARE @DocNumbers TABLE(C_Number INT)
DECLARE @NextNumber INT
DECLARE @MaxNextNumber INT

INSERT INTO @DocNumbers
-- получаем все используеммые номера за текущий год
SELECT df.C_Number
FROM

(SELECT (V1_100.RN_100-1)*100 +V2_100.RN_100 AS C_Number
FROM
(
SELECT (V1.RN - 1)*10 + V2.RN AS RN_100
FROM
(
SELECT 1 AS RN UNION ALL
SELECT 2 AS RN UNION ALL
SELECT 3 AS RN UNION ALL
SELECT 4 AS RN UNION ALL
SELECT 5 AS RN UNION ALL
SELECT 6 AS RN UNION ALL
SELECT 7 AS RN UNION ALL
SELECT 8 AS RN UNION ALL
SELECT 9 AS RN UNION ALL
SELECT 10 AS RN
) V1
CROSS JOIN
(SELECT 1 AS RN UNION ALL
SELECT 2 AS RN UNION ALL
SELECT 3 AS RN UNION ALL
SELECT 4 AS RN UNION ALL
SELECT 5 AS RN UNION ALL
SELECT 6 AS RN UNION ALL
SELECT 7 AS RN UNION ALL
SELECT 8 AS RN UNION ALL
SELECT 9 AS RN UNION ALL
SELECT 10 AS RN) V2
) V1_100
CROSS JOIN
(
SELECT (V1.RN - 1)*10 +V2.RN AS RN_100
FROM
(
SELECT 1 AS RN UNION ALL
SELECT 2 AS RN UNION ALL
SELECT 3 AS RN UNION ALL
SELECT 4 AS RN UNION ALL
SELECT 5 AS RN UNION ALL
SELECT 6 AS RN UNION ALL
SELECT 7 AS RN UNION ALL
SELECT 8 AS RN UNION ALL
SELECT 9 AS RN UNION ALL
SELECT 10 AS RN
) V1

CROSS JOIN
(
SELECT 1 AS RN UNION ALL
SELECT 2 AS RN UNION ALL
SELECT 3 AS RN UNION ALL
SELECT 4 AS RN UNION ALL
SELECT 5 AS RN UNION ALL
SELECT 6 AS RN UNION ALL
SELECT 7 AS RN UNION ALL
SELECT 8 AS RN UNION ALL
SELECT 9 AS RN UNION ALL
SELECT 10 AS RN
) V2
) V2_100
) AS DF
LEFT JOIN
(select distinct C_number,C_number2 from
(
SELECT
dd.C_NUMBER, dd.N_Number
,case when dd.C_Number is null or dd.C_Number = ' ' then 'a' else 'b' end C_number1
,CASE WHEN (ISNUMERIC(dd.C_Number) = 1 AND dd.C_Number NOT LIKE '%[.,]%') THEN CAST(dd.C_Number AS INT) END C_number2 -- только коректные номера
FROM dbo.DD_Docs dd
INNER JOIN dbo.DS_Docum_Types ddt ON ddt.LINK = dd.F_Docum_Types
where ddt.C_Const IN
('DST_ToleranceFirst', 'DST_ToleranceNext', 'DST_Checkup', 'DST_Return', 'DST_Survey', 'DST_Survey_TES', 'DST_Checkup_TES', 'DST_Return_TES', 'DST_ControlCheckup','DST_AnnulateAct','DST_TU_Giving', 'DST_UU_Matching')
and YEAR(dd.D_Register_Date) = YEAR(GETDATE()) ) t
where C_number not in ('a','БДИ')

) AS FD
ON DF.C_Number=FD.C_NUMBER
WHERE FD.C_NUMBER IS NULL

INSERT INTO XAF.CD_Bookings_Doc_Number
SELECT min(C_Number), GETDATE() from @DocNumbers
SELECT C_Number from XAF.CD_Bookings_Doc_Number
END

Но проблема осталась в том, что эта процедура складывает один и тот же номер, а нужно не занятый.
Подскажите, пожалуйста что и где нужно дописать или как сравнить две таблицы @DocNumbers и XAF.CD_Bookings_Doc_Number и записать в таблицу XAF.CD_Bookings_Doc_Number @DocNumbers +1, если min(C_Number)=XAF.CD_Bookings_Doc_Number.C_Number

Решила свою проблему вот таким образом