79635835

Date: 2025-05-23 15:52:44
Score: 2.5
Natty:
Report link

Good or bad, I went for the following:

set @v_xml_nest_level_pos = CHARINDEX ('<tsql_stack><frame nest_level', @p_additional_info, 0);

    -- nest_level  found
    if @v_xml_nest_level_pos > 0
    begin
        
    -- Replace single quotes with double quotes to make it valid XML
    set @xmlString = REPLACE(@p_additional_info, '''', '"');
    -- Now cast to XML
    set @xml = TRY_CAST(@xmlString AS XML);
    -- Extract nest_level as string
    set @nest_level_str =  @xml.value('(/tsql_stack/frame/@nest_level)[1]', 'NVARCHAR(100)');
    -- Try casting to INT safely
    SET @nest_level = TRY_CAST(@nest_level_str AS INT);

        if @nest_level is null
        begin
        set @P_SEO_CURR_USER = 1;
        end;
        if @nest_level is not null
        begin
            if @nest_level < 1
            begin
            set @P_SEO_CURR_USER = 1;
            end;
        end;
    
    end
    
    else -- nest_level not found
    
    begin
    set @P_SEO_CURR_USER = 1;
    end;

where:

p_additional_info - is field additional_information of fn_get_audit_file, within a loop of which the above code is run.

@P_SEO_CURR_USER is set as default 0, meaning non-top-level SQL. Set to 1 when top-level.

Thank you to all commenters who helped me on this.

best regards

Altin

Reasons:
  • Blacklisted phrase (0.5): Thank you
  • Blacklisted phrase (0.5): best regards
  • Blacklisted phrase (1): regards
  • Long answer (-1):
  • Has code block (-0.5):
  • User mentioned (1): @P_SEO_CURR_USER
  • Self-answer (0.5):
  • Low reputation (0.5):
Posted by: altink