logo
down
shadow

While looping through a function, Error: Maximum stored procedure, function, trigger, or view nesting level exceeded (li


While looping through a function, Error: Maximum stored procedure, function, trigger, or view nesting level exceeded (li

By : user2955926
Date : November 22 2020, 10:48 AM
I wish did fix the issue. Im going to assume that the purpose of this exercise is to split the values out of the columns so that you can then normalise your data.
regarding the actual error read @Amir's answer regarding helping you with solving your problem have a look at my approach below, hopefully you can pull something from that to help you.
code :
-- set up our sample datasource
declare @source as table 
(
    ProductNo int, 
    S1_CR varchar(100), 
    S1_CAT1 varchar(100), 
    S1_CAT2 varchar(100), 
    S1_CAT3 varchar(100), 
    S1_CAT4 varchar(100)
)

insert into @source values (1234, 'Prod1#Prod2#Prod3', '10#200#300', '20#34#400', '40#12#12', '50#23#12')

-- set up what will become input parameters to our sp
declare @prodNo     int = 1234
declare @code       varchar(10) = 'Prod2'
--Note that there are not Columns named Product, Year in the sample data but you refer to it in your Query so I assume they exist
declare @Product    varchar(10) -- is your declaration long enough? calling your sp you use 'ProductName1' which wont fit.
declare @year       varchar(4) = '2010'

-- this could be a constant if it doesnt change from record to record,
-- or a parameter if it does
declare @delimiter  char    = '#'

--declare our variables
declare @output     table
(
    ProductNo int, 
    S1_CR   varchar(100), 
    S1_CAT1 varchar(100), 
    S1_CAT2 varchar(100), 
    S1_CAT3 varchar(100), 
    S1_CAT4 varchar(100)
)

declare @ProductNo  int 
declare @S1_CR      varchar(100)
declare @S1_CAT1    varchar(100) 
declare @S1_CAT2    varchar(100) 
declare @S1_CAT3    varchar(100) 
declare @S1_CAT4    varchar(100)

--these ones are used during validation
declare @index      int = 1     
declare @term_count int = 0 -- this counts the number of terms in the column we are looking at
declare @num_terms  int = 0 -- this becomes our fixed comparison value
declare @reviewing  varchar(100) -- this is the data from the column we are looking at

select 
    @ProductNo = s.ProductNo,
    @S1_CR = s.S1_CR + @delimiter,
    @S1_CAT1 = s.S1_CAT1 + @delimiter,  
    @S1_CAT2 = s.S1_CAT2 + @delimiter,
    @S1_CAT3 = s.S1_CAT3 + @delimiter,
    @S1_CAT4 = s.S1_CAT4 + @delimiter
from @source s
where s.ProductNo = @prodNo

set @reviewing = @S1_CR

--validate to ensure there are equal number of concatenated terms in each column
--you will probably want to do other validation on inputs etc.
while @index > 0 --and @num_terms > 0
begin

    set @index = CHARINDEX(@delimiter, @reviewing, @index +1)

    if @index > 0 
        set @term_count = @term_count + 1
    else if @index = 0 and @term_count  @num_terms and @reviewing  @S1_CR -- num_terms will be zero when reviewing the first column
        set @num_terms = 0
    else 
        begin

            select 
                @num_terms = case @reviewing
                                when @S1_CR then @term_count
                                else @num_terms
                            end,
                @reviewing = case @reviewing
                                when @S1_CR then @S1_CAT1
                                when @S1_CAT1 then @S1_CAT2
                                when @S1_CAT2 then @S1_CAT3
                                when @S1_CAT3 then @S1_CAT4 
                                else ''
                            end

            if @reviewing = ''
                set @index = 0
            else
                set @index = 1
                set @term_count = 0
        end
end

--Split out the terms
while @num_terms > 0
begin    
    insert into @output values
    (
        @ProductNo,
        left(@S1_CR,CHARINDEX(@delimiter,@S1_CR,1) -1),
        left(@S1_CAT1,CHARINDEX(@delimiter,@S1_CAT1,1) -1),
        left(@S1_CAT2,CHARINDEX(@delimiter,@S1_CAT2,1) -1),
        left(@S1_CAT3,CHARINDEX(@delimiter,@S1_CAT3,1) -1),
        left(@S1_CAT4,CHARINDEX(@delimiter,@S1_CAT4,1) -1)
    )

    set @S1_CR = right(@S1_CR,len(@S1_CR) - CHARINDEX(@delimiter,@S1_CR,1))
    set @S1_CAT1 = right(@S1_CAT1,len(@S1_CAT1) - CHARINDEX(@delimiter,@S1_CAT1,1))
    set @S1_CAT2 = right(@S1_CAT2,len(@S1_CAT2) - CHARINDEX(@delimiter,@S1_CAT2,1))
    set @S1_CAT3 = right(@S1_CAT3,len(@S1_CAT3) - CHARINDEX(@delimiter,@S1_CAT3,1))
    set @S1_CAT4 = right(@S1_CAT4,len(@S1_CAT4) - CHARINDEX(@delimiter,@S1_CAT4,1))

    set @num_terms = @num_terms - 1
end

select * from @output where S1_CR = @code


Share : facebook icon twitter icon
Maximum stored procedure, function, trigger, or view nesting level exceeded in Visual Studio (ado.net) but not in SQL se

Maximum stored procedure, function, trigger, or view nesting level exceeded in Visual Studio (ado.net) but not in SQL se


By : MartinS
Date : March 29 2020, 07:55 AM
I wish this help you I have a problem with a recursive SQL function. The original problem is that I've got a list of employees, each of them has various trainings. Each of these trainings has some pre requirements. For example to have your Class 1 drivers license you must have your Class 5. If I remove the Class 5 I need to check disable the Class 1. , When you say:
Procedure Maximum stored procedure, function, trigger, or view nesting level exceeded (limit 32)

Procedure Maximum stored procedure, function, trigger, or view nesting level exceeded (limit 32)


By : XSLTNerd
Date : March 29 2020, 07:55 AM
I think the issue was by ths following , The stored proc is failing at below location,, for all your help. , Each call to EXEC GetMSOrg keeps progressing along the query
code :
SELECT  @ReportsToPersonnelNbr = ReportsToPersonnelNbr
FROM        ReportsTo
WHERE   PersonnelNumber = @PersonnelNumber
declare @PersonnelNumber int
set @PersonnelNumber = 10

;with CTE as (
select 1 Level,
       convert(varchar(max),@PersonnelNumber) Seed,
       '>>' + convert(varchar(max),@PersonnelNumber) + '>>' Path
union all
SELECT Level+1,
       convert(varchar(max),ReportsToPersonnelNbr),
       Path + convert(varchar(max),ReportsToPersonnelNbr) + '>>'
FROM   ReportsTo
join CTE on CTE.Seed = ReportsTo.PersonnelNumber
where  Level < 40
)
select *
from CTE
where Len(Replace(Path, '>' + convert(varchar(max),Seed) + '>', ''))
  = LEN(Path) - Len('>' + convert(varchar(max),Seed) + '>') * 2
factorial function error - Maximum stored procedure, function, trigger, or view nesting level exceeded

factorial function error - Maximum stored procedure, function, trigger, or view nesting level exceeded


By : CnaSoft
Date : March 29 2020, 07:55 AM
it helps some times Recursive computations aren't where SQL Server (nor any other database) shines. The best solution is to move this out of the database and calculate it in your application. But if you absolutely must do this in the database, use an iterative method instead of recursive one, for example:
code :
create function dbo.factorial(@num1 float(53))
returns float(53)
as
begin
    declare @factno bigint;

    ;With Nums As
    (
        select ROW_NUMBER() OVER (ORDER BY (SELECT 0)) AS RN
        FROM sys.objects
    )
    SELECT @factno = POWER(10.0, SUM(LOG10(RN)))
    FROM Nums
    WHERE RN <= @num1

    return @factno;
end
Maximum stored procedure, function, trigger, or view nesting level exceeded (limit 32)

Maximum stored procedure, function, trigger, or view nesting level exceeded (limit 32)


By : Ajay Bansod
Date : March 29 2020, 07:55 AM
Any of those help I am creating a store procedure but while executing the procedure i am getting the particular error. , Use the "Go" after the END statement
Maximum stored procedure, function, trigger, or view nesting level exceeded (limit 32) using SQL Server 2014

Maximum stored procedure, function, trigger, or view nesting level exceeded (limit 32) using SQL Server 2014


By : Aristoteles
Date : March 29 2020, 07:55 AM
I hope this helps . Make sure that nested triggers is disabled. How to disable and enable nested triggers, can be found in the microsoft documentation.
https://docs.microsoft.com/en-us/sql/database-engine/configure-windows/configure-the-nested-triggers-server-configuration-option
Related Posts Related Posts :
  • FreeTDS working, but ODBC cannot connect
  • How to change datetime format of varchar datatype column
  • Trigger on extended properties? SQL SERVER
  • Database Schema guidance for an app store
  • sql trigger to update based on specific column is updated and if value is equal to 'x'
  • How to descope a local variable in t-sql
  • SQL Server Compact stored procedure with multiple values in 1 input
  • How to validate the ICCID number of a SIM card in TSQL?
  • how to calculate all previous datas sum of a sql column on a date range report
  • How to create view listing all friendship for "friend1,friend2" table in T-SQL and prevent inserting duplicate
  • Sum of multiple column in SQL Server
  • Check month and day lies between two dates
  • How would I configure Effort Testing Tool to mock Entity Framework's DbContext withOut the actual SQL Server Database up
  • Collapsing records with adjacent start and end dates
  • SQL Server Management Studio 2008 - Timeout Expired error
  • Insert date from VB to SQL Server
  • How to find what table foreign key is from?
  • Sql Server Nested Inserts possible
  • Deterministic way of getting WeekEnding (Sun) for given date column (for use in computed column)
  • SQL Server, why does one order by query work with a string, but not with an integer?
  • How do I install a SQL Server on the hosted build agent in Visual Studio Online?
  • EXECUTE AS/REVERT within a transaction in SQL Server
  • Distinct 2 field AS one column SQL SERVER
  • how to create a new database after a trigger fired from a table
  • How to sort string alphabetically
  • Checking data in one column in a temp table to a column in another table
  • What's wrong with my IF/ELSE? "ELSE: Incorrect syntax near 'ELSE'."
  • CASE CLAUSE IN WHERE CONDITION
  • TSQL - Creating a running total of a column
  • Why am I getting not null here?
  • Why does sp_refreshview raise an exception on views that are not schema bound?
  • EXEC parameters from a SELECT
  • SQL Server Compact connection
  • Star Schema Structure - To many Dimensions
  • sort utf string in sql server with join clause
  • shadow
    Privacy Policy - Terms - Contact Us © ourworld-yourmove.org