Nesting Limit
-
Assume
@Codeis a three character prefix, ie:AUSWhy does this break:DECLARE @TEMP VARCHAR(7) =
(SELECT TOP 1 CustCode
FROM Customer
WHERE LEFT(CustCode, 3) = @Code)with the following error?
Maximum stored procedure, function, trigger, or view nesting level exceeded (limit 32).
I don't speak Idiot - please talk slowly and clearly 'This space for rent' Driven to the arms of Heineken by the wife
-
Assume
@Codeis a three character prefix, ie:AUSWhy does this break:DECLARE @TEMP VARCHAR(7) =
(SELECT TOP 1 CustCode
FROM Customer
WHERE LEFT(CustCode, 3) = @Code)with the following error?
Maximum stored procedure, function, trigger, or view nesting level exceeded (limit 32).
I don't speak Idiot - please talk slowly and clearly 'This space for rent' Driven to the arms of Heineken by the wife
I suspect there are more than 1 record matching your @code filter and you are trying to stuff more than 1 record into @Temp. Nope - thats not it, you have top 1. It works for me on sql server 2008 R2. I used this
DECLARE
@Code VARCHAR(3)SET @Code = '103'
DECLARE @TEMP VARCHAR(7) =
(SELECT TOP 1 filename
FROM dbo.ImportFiles
WHERE LEFT(filename, 3) = @Code)SELECT @Temp
Which gives me the first 7 characters of the filename (all the file names start with 103). Changing @Code returns null
Never underestimate the power of human stupidity RAH
-
I suspect there are more than 1 record matching your @code filter and you are trying to stuff more than 1 record into @Temp. Nope - thats not it, you have top 1. It works for me on sql server 2008 R2. I used this
DECLARE
@Code VARCHAR(3)SET @Code = '103'
DECLARE @TEMP VARCHAR(7) =
(SELECT TOP 1 filename
FROM dbo.ImportFiles
WHERE LEFT(filename, 3) = @Code)SELECT @Temp
Which gives me the first 7 characters of the filename (all the file names start with 103). Changing @Code returns null
Never underestimate the power of human stupidity RAH
Thanks for looking Mycroft - seems the issue is a calculated field (CustCode) that is part of the table definition.
CREATE TABLE Customer(
...
CustCode AS udf_GetCustCode(@Name),
...I think this needs to be calculated as a udf during the INSERT.
I don't speak Idiot - please talk slowly and clearly 'This space for rent' Driven to the arms of Heineken by the wife
-
Thanks for looking Mycroft - seems the issue is a calculated field (CustCode) that is part of the table definition.
CREATE TABLE Customer(
...
CustCode AS udf_GetCustCode(@Name),
...I think this needs to be calculated as a udf during the INSERT.
I don't speak Idiot - please talk slowly and clearly 'This space for rent' Driven to the arms of Heineken by the wife
Andy_L_J wrote:
seems the issue is a calculated field (CustCode) that is part of the table definition.
Probably fired off by a trigger - triggers how to completely fuck your database.
Never underestimate the power of human stupidity RAH