Maximum stored procedure, function, trigger, or view nesting level exceeded (limit 32) in function checking SwissQR references

We have a new iso for payment. These payment needs to contain a 'QR reference' field made of 26 + 1 digits. The last one is a validation digit obtained using a recursive function.

I created a SQL function allowing us to know wether a 'QR reference' is valid or not:

create  function [dbo].[fnCheckQRReference](@Reference nvarchar(28), @Range int = 0)
returns bit as
/* Check if the SwissQR payment reference is correct
 * Return 1 if QR Reference is correct
 * Return 0 if not
 */
begin
	if not(@Reference not like '%[^0-9]%')
		return 0
	if len(@Reference) > 27
		return 0

	declare   @tmp as	int = cast(substring(@Reference, 1, 1) as int)
		, @result	as	int;

	with tab as (
		select 0 as [id], 0 as [val] union select 1, 9 union select 2, 4 union select 3, 6 union select 4, 8 union select 5, 2 union select 6, 7 union select 7, 1 union select 8, 3 union select 9, 5
	)
	select	@result = val
	from	tab
	where	id	= (@Range + @tmp) % 10

	if len(@Reference) = 2
		if	(((10 - @result) % 10) = cast(substring(@Reference, 2, 26) as int))
			return 1
		else
			return 0

	return	[dbo].[fnCheckQRReference](substring (@Reference, 2, 26), @result);
end
go

You can test it with different references (000000810877053913500061524, 903751000001009522863019583, 000000000000000002023031440, 000000000000000000002185921, etc.)

My problem reside later when I want to select drafts of incoming invoices

select	  cast(ii.InvoiceNumber as nvarchar(15))		as	'Invoice number'
		, ii.QRReference								as	'QR_Reference'
		, [dbo].[fnCheckQRReference](ii.QRReference, 0)	as	'Ref_OK'
--
from	Incoming_Invoices		as	ii	with(nolock)
--
where   ii.InvoiceStatus not in ('D', 'E')	-- Looking only for drafts and error statuses

When executing the previous query, I get the error:
> Maximum stored procedure, function, trigger, or view nesting level exceeded (limit 32).

What I do not understand here is that the function can only call itself 26 times, never 32.

I tried adding a top 1 in my last query but still, SQL Server answered that the nesting level exceeded the limit of 32.

When I commented the , [dbo].[fnCheckQRReference](ii.QRReference, 0) as 'Ref_OK' line, I had 46 result out of which only one is in error. (Ref*_*OK = 0)

Does someone see where is my mistake(s)?

You'll need to switch from recursion to iteration. 28 levels of recursion is too much to be reliable, because you only have a few frames for the calling code.

In a loop

set @Reference = substring(@Reference, 2, 26)

until you've consumed the whole string.

So I did as you told me. First, I created a view containing the required data to calculate the last digit:

create VIEW [custom].[QR_code_modulo_digit]
AS
select 0 as [id], 0 as [val] union select 1, 9 union select 2, 4 union select 3, 6 union select 4, 8 union select 5, 2 union select 6, 7 union select 7, 1 union select 8, 3 union select 9, 5

Then my function with a 'while' loop :

alter function [dbo].[fnCheckQRReference](@Reference nvarchar(28))
returns bit as
/* Check if the reference sent by Esker for QR payment is correct
 * Return 1 if QR Reference is correct
 * Return 0 if not
 */
begin
	if not(@Reference not like '%[^0-9]%')
		return 0
	if len(@Reference) != 27
		return 0

	declare   @tmp		as	int = cast(substring(@Reference, 1, 1) as int)
			, @result	as	int = 0;
	
	while (len(@Reference) > 1)
	begin
		select	@result		= val	from	[custom].[QR_code_modulo_digit]	where	id	= (@tmp + @result) % 10
		set		@Reference	=		substring(@Reference, 2, 26);
		set		@tmp		= cast(	substring(@Reference, 1, 1) as int);
	end

	if	(((10 - @result) % 10) = cast(@Reference as int))
		return 1
	
	return 0
end
go

It is a little less elegant than my previous recursiv function but works fine and it has some good sides such as the fact that the function has ony one parameter and we can test it only once.

Thank you Davide Browne from Microsoft!