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

eiee3dmh 于 2023-03-11 发布在其他

关注(0)|答案(2)|浏览(200)

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)?

sql-server

来源：https://stackoverflow.com/questions/75684954/maximum-stored-procedure-function-trigger-or-view-nesting-level-exceeded-lim

2条答案

按热度按时间

j2qf4p5b1#

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.

赞(0）回复(0）举报 2023-03-11

deyfvvtc2#

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!

赞(0）回复(0）举报 2023-03-11

我来回答

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

2条答案

相关问题

热门标签

最新问答