December 23, 2015 at 10:51 am
Hi guys,
I am doing one POC. I am trying to generate and execute a dynamic sql. In that dynamic sql i have to many replace functions.At the time of execute it is giving me error as
[highlight="ff0000"]Some part of your SQL statement is nested too deeply. Rewrite the query or break it up into smaller queries.[/highlight]
Any help would be much appreciated.
Thank you very much,
Re1
December 23, 2015 at 10:59 am
Is this error being thrown from SQL, or C#?
Can you post the code? We can't really see what you see.
Michael L John
If you assassinate a DBA, would you pull a trigger?
To properly post on a forum:
http://www.sqlservercentral.com/articles/61537/
December 23, 2015 at 11:35 am
Re1 (12/23/2015)
Hi guys,I am doing one POC. I am trying to generate and execute a dynamic sql. In that dynamic sql i have to many replace functions.At the time of execute it is giving me error as
[highlight="ff0000"]Some part of your SQL statement is nested too deeply. Rewrite the query or break it up into smaller queries.[/highlight]
Any help would be much appreciated.
Thank you very much,
Re1
Based on the detail you have provided, I would suggest that you rewrite your dynamic SQL such that the number of nesting levels is reduced.
The absence of evidence is not evidence of absence.
Martin Rees
You can lead a horse to water, but a pencil must be lead.
Stan Laurel
December 25, 2015 at 2:01 am
HI guys,
Below is the sample Query
--Table Script-----
CREATE TABLE TblMain
(
ID INT IDENTITY(1,1),
Value NVarchar(MAX),
business_unit_list NVarchar(MAX),
Region_list NVarchar(MAX),
category_key_list NVarchar(MAX)
)
INSERT INTO TblMain
SELECT 'Value1','2200,2201,2202,2203,2204','1,3,11,13,14,15,16,17,18','486750000001,486750000002,486750000003' UNION ALL
SELECT 'Value2','2208,2209,2210,2211,2212,2213','45,46,47,48,49,50,51,52,','486750000009,486750000010,486750000011,486750000012' UNION ALL
SELECT 'Value3','2217,2218,2219,2220,2221,2222,2223,2224,2225,2226,2227,2228,2229,2232,2235,2236,2237,223','56,57,58,59,60,61,62,63,64,65,66,67,68,69,70,71,72,73,74,','486750000006,486750000007,486750000008,486750000009,486750000010,486750000011,486750000012,486750000013,486750000014,486750000015,486750000016,486750000017,486750000018,486750000019,486750000020,486750000021,486750000022,486750000023,486750000024,486750000025,486750000026,486750000027,486750000028,486750000029,486750000030,486750000031,486750000032,486750000033,486750000034,486750000035,486750000036,486750000037,486750000038,486750000039,486750000040,486750000041,486750000042'
-----------------------------------------Scrpit------------------------------------------
DECLARE @I INT=1,@MAX INT,@End INT,@offset int;
DECLARE @PASCodes tvp_RPT_UserAccess
DECLARE @RegionCodes tvp_RPT_UserAccess
DECLARE @BUCodes tvp_RPT_UserAccess
DECLARE @Sql_BU_Replace NVARCHAR(MAX)=''
DECLARE @Sql_PAS_Replace NVARCHAR(MAX)=''
DECLARE @Sql_Region_Replace NVARCHAR(MAX)=''
DECLARE @Sql_Query NVARCHAR(MAX)
DECLARE @data NVARCHAR(MAX)
INSERT INTO @BUCodes
SELECT ITEM
FROM [dbo].[Split] ('2200,2201,2202,2203,2204,2205,2206,2207,2208,2209,2210,2211,2212,2213,2214,2215,2216,2217,2218,2219,2220,2221,2222,2223,2224,2225,2226,2227,2228,2229,2232,2235,2236,2237,2238,2239,2240,2241,2242,2243,2244,2245,2246,2247,2248,2249,2250,2251,2252,2253,2254,2255,2256,2257,2258,2259,2260,2261,2262,2263,2264,2265,2266,2267,2268,2269,2271,2272,2273,2274,2275,2276,2277,2278,2279,2280,2281,2282,2283,2284,2285,2286,2287,2288,2289,2290,2291,2292,2293,2294,2295,2296,2297,2298,2299,2300,2301,2302,2303,2304,2305,2306,2307,2308,2309,2310,2311,2312,2313,2314,2315,2316,2317,2318,2319,2320,2321,2322,2323,2324,2325,2326,2327,2328,2329,2330,2331,2332,2333,2334,2335,2336,2337,2338,2339,2340,2341,2342,2343,2344,2345,2346,2347,2348,2349,2350,2351,2352,2353,2354,2355,2356,2357,2358,2359,2360,2361,2362,2363,2364,2365,2366,2367,2368,2369,2370,2371,2372,2373,2374,2375,2376,2377,2378,2379,2380,2381,2382,2383,2384,2385,2386,2387,2388,2389,2390,2391,2392,2393,2394,2395,2396,2397,2398,2399,2400,2401,2402,2403,2404,2405,2406,2407,2408,2409,2410,2411,2412,2413,2414,2415,2416,2417,2418,2419,2420,2421,2422,2423,2424,2425,2426,2427,2428,2429,2430,2431,2432,2433,2434,2435,2436,2437,2438,2439,2440,2441,2442,2443,2444,2445,2446,2447,2448,2449,2450,2451,2452,2453,2454,2455,2456,2457,2458,2459,2460,2461,2462,2463,2464,2465,2466,2467,2468,2469,2470,2471,2472,2473,2474,2475,2476,2477,2478,2479,2480,2481,2482,2483,2484,2485,2486,2487,2488,2489,2490,2491,2492,2493,2494,2495,2496,2497,2498,2499,2500,2501,2502,2503,2504,2505,2506,2507,2508,2509,2510,2511,2512,2513,2514,2515,2516,2517,2518,2519,2520,2521,2522,2523,2524,2525,2526,2527,2528,2529,2530,2531,2532,2533,2534,2535,2536,2537,2538,2539,2540,2541,2542,2543,2544,2545,2546,2547,2548,2549,2550,2551,2552,2553,2554,2555,2556,2557,2558,2559,2560,2561,2562,2563,2564,2565,2566,2567,2568,2569,2570,2571,2572,2573,2574,2575,2576,2577,2578,2579,2580,2581,2582,2583,2584,2585,2586,2587,2588,2589,2590,2591,2592,2593,2594,2595,2596,2597,2598,2599,2600,2601,2602,2603,2604,2605,2606,2607,2608,2609,2610,2611,2612,2613,2614,2615,2616,2617,2618,2619,2620,2621,2622,2623,2624,2625,2626,2627,2628,2629,2630,2631,2632,2633,2634,2635,2636,2637,2638,2639,2640,2641,2642,2643,2644,2645,2646,2647,2649,4390,4392,4393,4394,4395,4396,4398,4399,4404,4446,4453,4454,4455,4456,4457,4458,4459,4460,4461,4466,4467,4470,4472,4476,4477,4478',',')
INSERT INTO @RegionCodes
SELECT ITEM
FROM [dbo].[Split] ('1,3,11,13,14,15,16,17,18,19,20,23,24,25,26,28,30,31,32,33,34,35,36,38,39,40,42,43,44,45,46,47,48,49,50,51,52,53,54,55,56,57,58,59,60,61,62,63,64,65,66,67,68,69,70,71,72,73,74,75,76,77,78,79,80,82,83,84,85,86,87,88,89,90,91,92,93,94,95,96,97,98,99,100,101,102,103,104,105,106,107,108,109,110,111,112,113,114,115,116,117,118,119,120,121,122,123,124,125,126,127,128,129,130,131,132,133,134,135,136,137,138,139,140,141,142,143,144,145,146,147,148,149,150,151,152,153,154,155,156,157,158,159,160,161,162,163,164,165,166,167,168,169,170,171,172,173,174,175,176,177,178,179,180,181,182,183,184,187,188,189,190,191,192,193,194,195,196,197,198,199,200,201,202,203,204,205,206,207,208,210,211,213,214,216,217,218,220,221,222,223,224,225,226,228,229,231,232,233,234,235,236,237,238,239,240,241,242,243,245,246,247,248,249,250,251,252,253,254,255,256,257,258,259,260,261,262,263,264,265,266,267,269,270,271,272,273,274,275,276,277,278,279,280,281,282,283,284,285,286,287,288,289,290,291,292,293,294,295,296,297,298,299,300,301,302,303,304,305,306,308,309,310,312,314,315,316,317,318,319,320,321,322,324,325,326,327,328,329,330,331,332,333,334,335,336,337,339,340,341,342,343,344,346,347,348,349,350,351,352,353,354,355,356,357,358,359,361,362,363,364,365,366',',')
INSERT INTO @PASCodes
SELECT ITEM
FROM [dbo].[Split] ('486750000001,486750000002,486750000003,486750000004,486750000005,486750000006,486750000007,486750000008,486750000009,486750000010,486750000011,486750000012,486750000013,486750000014,486750000015,486750000016,486750000017,486750000018,486750000019,486750000020,486750000021,486750000022,486750000023,486750000024,486750000025,486750000026,486750000027,486750000028,486750000029,486750000030,486750000031,486750000032,486750000033,486750000034,486750000035,486750000036,486750000037,486750000038,486750000039,486750000040,486750000041,486750000042,486750000043,486750000044,486750000045,486750000046,486750000047,486750000048,486750000049,486750000050,486750000051,486750000052,486750000053,486750000054,486750000055,486750000056,486750000057,486750000058,486750000059,486750000060,486750000061,486750000062,486750000063,486750000064,486750000065,486750000066,486750000067,486750000068,486750000069,486750000070,486750000071,486750000072,486750000073,486750000074,486750000075,486750000076,486750000077,486750000078,486750000079,486750000080,486750000081,486750000082,486750000083,486750000084,486750000085,486750000086,486750000087,486750000088,486750000089,486750000090,486750000091,486750000092,486750000093,486750000094,486750000095,486750000096,486750000097,486750000098,486750000099,486750000100,486750000101,486750000102,486750000103,486750000104,486750000105,486750000106,486750000107,486750000108,486750000109,486750000110,486750000111,486750000112,486750000113,486750000114,486750000115,486750000116,486750000117,486750000118,486750000119,486750000120,486750000121,486750000122,486750000123,486750000124,486750000125,486750000126,486750000127,486750000128,486750000129,486750000130,486750000131,486750000132,486750000133,486750000134,486750000135,486750000136,486750000137,486750000138,486750000139,486750000140,486750000141,486750000142,486750000143,486750000144,486750000145,486750000146,486750000147,486750000148,486750000149,486750000150,486750000151,486750000152,486750000153,486750000154,486750000155,486750000156,486750000157,486750000158,486750000159,486750000160,486750000161,486750000162,486750000163,486750000164,486750000165,486750000166,486750000167,486750000168,486750000169,486750000170,486750000171,486750000172,486750000173,486750000174,486750000175,486750000176,486750000177,486750000178,486750000179,486750000180,486750000181,486750000182,486750000183,486750000184,486750000185,486750000186,486750000187,486750000188,486750000189,486750000190,486750000191,486750000192,486750000193,486750000194,486750000195,486750000196,486750000197,486750000198,486750000199,486750000200,486750000201,486750000202,486750000203,486750000204,486750000205,486750000206,486750000207,486750000208,486750000209,486750000210,486750000211,486750000212,486750000213,486750000214,486750000215,486750000216,486750000217,486750000218,486750000219,486750000220,486750000221,486750000222,486750000223,486750000224,486750000225,486750000226,486750000227,486750000228,486750000229,486750000230,486750000231,486750000232,486750000233,486750000234,486750000235,486750000236,486750000237,486750000238,486750000239,486750000240,486750000241,486750000242,486750000243,486750000244,486750000245,486750000246,486750000247,486750000248,486750000249,486750000250,486750000251,486750000252,486750000253,486750000254,486750000255,486750000256,486750000257,486750000258,486750000259,486750000260,486750000261,486750000262,486750000263,486750000264,486750000265,486750000266,486750000267,486750000268,486750000269,486750000270,486750000271,486750000272,486750000273,486750000274,486750000275,486750000276,486750000277,486750000278,486750000279,486750000280,486750000281,486750000282,486750000283,486750000284,486750000285,486750000286,486750000287,486750000288,486750000289,486750000290,486750000291,486750000292,486750000293,486750000294,486750000295,486750000296,486750000297,486750000298,486750000299,486750000300,486750000301,486750000302,486750000303,486750000304,486750000305,486750000306,486750000307,486750000308,486750000309,486750000310,486750000311,486750000312,486750000313,486750000314,486750000315,486750000316,486750000317,486750000318,486750000319,486750000320,486750000321,486750000322,486750000323,486750000324,486750000325,486750000326,486750000327,486750000328,486750000329,486750000330,486750000331,486750000332,486750000333,486750000334,486750000335,486750000336,486750000337,486750000338,486750000339,486750000340,486750000341,486750000342,486750000343,486750000344,486750000345,486750000346,486750000347,486750000348,486750000349,486750000350,486750000351,486750000352,486750000353,486750000354,486750000355,486750000356,486750000357,486750000358,486750000359,486750000360,486750000361,486750000362,486750000363,486750000364,486750000365,486750000366,486750000367,486750000368,486750000369,486750000370,486750000371,486750000372,486750000373,486750000374,486750000375,486750000376,486750000377,486750000378,486750000379,486750000380,486750000381,486750000382,486750000383,486750000384,486750000385,486750000386,486750000387,486750000388,486750000389,486750000390,486750000391,486750000392,486750000393,486750000394,486750000395,486750000396,486750000397,486750000398,486750000399,486750000400,486750000401,486750000402,486750000403,486750000404,486750000405,486750000406,486750000407,486750000408,486750000409,486750000410,486750000411,486750000412,486750000413,486750000414,486750000415,486750000416,486750000417,486750000418,486750000419,486750000420,486750000421,486750000422,486750000423,486750000424,486750000425,486750000426,486750000427,486750000428,486750000429,486750000430,486750000431,486750000432,486750000433,486750000434,486750000435,486750000436,486750000437,486750000438,486750000439,486750000440,486750000441,486750000442,486750000443,486750000444,486750000445,486750000446,486750000447,486750000448,486750000449,486750000450,486750000451,486750000452,486750000453,486750000454,486750000455,486750000456,486750000457,486750000458,486750000459,486750000460,486750000461,486750000462,486750000463,486750000464,486750000465,486750000466,486750000467,486750000468,486750000469,486750000470,486750000471,486750000472,486750000473,486750000474,486750000475,486750000476,486750000477,486750000478,486750000479,486750000480,486750000481,486750000482,486750000483,486750000484,486750000485,486750000486,486750000487,486750000488,486750000489,486750000490,486750000491,486750000492,486750000493,486750000494,486750000495,486750000496,486750000497,486750000498,486750000499,486750000500,486750000501,486750000502,486750000503,486750000504,486750000505,486750000506,486750000507,486750000508,486750000509,486750000510',',')
-----------------------BU--------------------------
SET @Sql_BU_Replace='business_unit_list'--- This is a column name for bu replace
IF OBJECT_ID('tempdb..#Bulist') IS NOT NULL
BEGIN
DROP TABLE #Bulist
END
CREATE TABLE #Bulist
(
id int IDENTITY(1,1) NOT NULL,
data nvarchar(max)
)
INSERT INTO #Bulist(data)
SELECT
ObjectCode
FROM
@BUCodes
SELECT @max-2=MAX(id) from #Bulist
WHILE @i<=@MAX
BEGIN
SELECT @data=data FROM #Bulist Where id=@I
SET @Sql_BU_Replace='REPLACE('+ @Sql_BU_Replace+','''+@Data +''',''*'')'
SET @i=@i + 1
END
SET @I=1
--------------------Category---------------------
--Below Code is to create replace string for Category Codes
SET @Sql_PAS_Replace='category_key_list'--- This is a column name for category replace
IF OBJECT_ID('tempdb..#PASlist') IS NOT NULL
BEGIN
DROP TABLE #PASlist
END
CREATE TABLE #PASlist
(
id int IDENTITY(1,1) NOT NULL,
data nvarchar(max)
)
INSERT INTO #PASlist(data)
SELECT
ObjectCode
FROM
@PASCodes
SELECT @max-2=MAX(id) from #PASlist
WHILE @i<=@MAX
BEGIN
SELECT @data=data FROM #PASlist Where id=@I
SET @Sql_PAS_Replace='REPLACE('+ @Sql_PAS_Replace+','''+@Data +''',''*'')'
SET @i=@i + 1
END
PRINT '@Sql_PAS_Replace:- ' + @Sql_PAS_Replace
SET @I=1
------------------------Region----------------------
--Below Code is to create replace string for Region Codes
SET @Sql_Region_Replace='Region_list'--- This is a column name for region replace
IF OBJECT_ID('tempdb..#Regionlist') IS NOT NULL
BEGIN
DROP TABLE #Regionlist
END
CREATE TABLE #Regionlist
(
id int IDENTITY(1,1) NOT NULL,
data nvarchar(max)
)
INSERT INTO #Regionlist(data)
SELECT
ObjectCode
FROM
@RegionCodes
WHILE @i<=@MAX
BEGIN
SELECT @data=data FROM #Regionlist Where id=@I
SET @Sql_Region_Replace='REPLACE('+ @Sql_Region_Replace+','''+@Data +''',''*'')'
SET @i=@i + 1
END
PRINT '@Sql_Region_Replace:- ' + @Sql_Region_Replace
SET @Sql_Query=
'SELECT
*
FROM
TblMain
Where
' + @Sql_BU_Replace + ' Like ''%*%''
AND
' + @Sql_PAS_Replace + ' Like ''%*%''
AND
' + @Sql_Region_Replace + ' Like ''%*%'''
EXEC(@Sql_Query)
December 25, 2015 at 3:24 am
My suggestion is to rewrite this query, quite certain that there is no need for neither dynamic sql nor the nested replace. The syntax is correct but the methods are terribly inefficient.
😎
Can you explain the business logic behind this code?
December 28, 2015 at 5:45 am
Hi Eirikur Eiriksson,
In out current t-sql we are using tvp, which is taking so much time to produce the o/p.
We are trying to reduce one to many relation in two table with comma separated valued in the table.
The using replace function it will show only the matching records.
December 28, 2015 at 10:24 am
It seems like your code can be rewritten like this
SELECT *
FROM TblMain m
Where EXISTS ( SELECT 1
FROM [dbo].DelimitedSplit8K( @BUCodesString, ',') s
WHERE m.business_unit_list LIKE '%' + s.Item + '%')
AND EXISTS ( SELECT 1
FROM [dbo].DelimitedSplit8K( @PASCodesString, ',') s
WHERE m.category_key_list LIKE '%' + s.Item + '%')
AND EXISTS ( SELECT 1
FROM [dbo].DelimitedSplit8K( @RegionCodesString, ',') s
WHERE m.Region_list LIKE '%' + s.Item + '%')
EDIT: This can be done with the table-valued parameters and should work even better. Although, the best suggestion is to normalize your tables.
December 29, 2015 at 10:59 am
Thanks Luis it works fine
December 29, 2015 at 11:11 am
Luis Cazares (12/28/2015)
It seems like your code can be rewritten like this
SELECT *
FROM TblMain m
Where EXISTS ( SELECT 1
FROM [dbo].DelimitedSplit8K( @BUCodesString, ',') s
WHERE m.business_unit_list LIKE '%' + s.Item + '%')
AND EXISTS ( SELECT 1
FROM [dbo].DelimitedSplit8K( @PASCodesString, ',') s
WHERE m.category_key_list LIKE '%' + s.Item + '%')
AND EXISTS ( SELECT 1
FROM [dbo].DelimitedSplit8K( @RegionCodesString, ',') s
WHERE m.Region_list LIKE '%' + s.Item + '%')
EDIT: This can be done with the table-valued parameters and should work even better. Although, the best suggestion is to normalize your tables.
Very nice solution Luis!
😎
January 4, 2016 at 3:34 pm
Re1 (12/29/2015)
Thanks Luis it works fine
Hi, did you make use of the "DelimitedSplit8K" function?
----------------------------------------------------
January 4, 2016 at 6:24 pm
Heh... I'll bet credits to Navy beans that the [SPLIT] function has either a WHILE Loop or some nasty XML in it with odds-on favorite being the WHILE Loop.
--Jeff Moden
Change is inevitable... Change for the better is not.
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply