SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2008 Forums
 Transact-SQL (2008)
 nvarchar max is not unlimited
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

esthera
Flowing Fount of Yak Knowledge

1388 Posts

Posted - 09/27/2012 :  05:17:24  Show Profile  Reply with Quote
it seems my nvarchar max is cutting

see the following code - it always shows only 1001



declare @holdoff nvarchar(max)
select @holdoff='282990, 282991, 283012, 283014, 283016, 283017, 283018, 283019, 283020, 283021, 283022, 283023, 283024, 283025, 283026, 283027, 283028, 283029, 283031, 283034, 286574, 287154, 287240, 287272, 287457, 287690, 287732, 287744, 287843, 287966, 288227, 288228, 288443, 288842, 288938, 289010, 289339, 289709, 289987, 290101, 290107, 290108, 290238, 290251, 291682, 291735, 291803, 291826, 291922, 291952, 291966, 291979, 292073, 292568, 292618, 292626, 292679, 292715, 292800, 292817, 292835, 292836, 292837, 292838, 292840, 292842, 292843, 292844, 292845, 292846, 292847, 292848, 292849, 292850, 292851, 292852, 292853, 292854, 292855, 292856, 292857, 292858, 292859, 292860, 292861, 292862, 292863, 292864, 292865, 292866, 292867, 292868, 292869, 292870, 292871, 292873, 292874, 292875, 292876, 292894, 292895, 292897, 292898, 292899, 292900, 292901, 292902, 292903, 292904, 292905, 292906, 292908, 292909, 292912, 292913, 292914, 292915, 292916, 292918, 292919, 292920, 292921, 292922, 292923, 292924, 292925, 292926, 292927, 292936, 292938, 292939, 292942, 292943, 292944, 292945, 292946, 292955, 292966, 292980, 293099, 293100, 293101, 293102, 293103, 293104, 293105, 293106, 293107, 293108, 293109, 293110, 293111, 293112, 293113, 293114, 293115, 293116, 293117, 293118, 293119, 293120, 293121, 293122, 293123, 293124, 293125, 293126, 293127, 293149, 293150, 293154, 293155, 293156, 293157, 293158, 293181, 293182, 293189, 293193, 293194, 293196, 293197, 293198, 293199, 293200, 293202, 293210, 293214, 293216, 293217, 293218, 293219, 293220, 293221, 293226, 293230, 293231, 293232, 293233, 293238, 293239, 293241, 293242, 293243, 293244, 293245, 293246, 293247, 293248, 293249, 293250, 293251, 293252, 293253, 293254, 293255, 293256, 293257, 293258, 293297, 293301, 293308, 293309, 293310, 293311, 293312, 293313, 293314, 293315, 293316, 293317, 293318, 293319, 293320, 293321, 293322, 293323, 293324, 293325, 293326, 293327, 293328, 293344, 293345, 293346, 293347, 293348, 293349, 293351, 293352, 293353, 293354, 293355, 293356, 293357, 293369, 293381, 293382, 293383, 293384, 293385, 293386, 293387, 293388, 293389, 293390, 293391, 293392, 293393, 293395, 293396, 293397, 293398, 293399, 293400, 293401, 293403, 293406, 293407, 293408, 293411, 293412, 293413, 293414, 293415, 293416, 293417, 293418, 293419, 293420, 293421, 293422, 293423, 293428, 293429, 293430, 293431, 293433, 293439, 293441, 293442, 293444, 293445, 293447, 293464, 293465, 293466, 293467, 293468, 293469, 293470, 293471, 293472, 293473, 293474, 293475, 293476, 293477, 293478, 293480, 293481, 293482, 293483, 293484, 293485, 293486, 293487, 293488, 293489, 293490, 293491, 293496, 293497, 293498, 293499, 293501, 293502, 293503, 293504, 293505, 293506, 293507, 293508, 293509, 293510, 293511, 293512, 293513, 293514, 293515, 293516, 293517, 293518, 293553, 293554, 293555, 293565, 293566, 293567, 293568, 293569, 293570, 293572, 293573, 293574, 293575, 293577, 293578, 293579, 293583, 293584, 293586, 293587, 293589, 293591, 293592, 293593, 293594, 293597, 293598, 293602, 293603, 293604, 293605, 293606, 293607, 293608, 293609, 293610, 293611, 293612, 293613, 293614, 293615, 293616, 293617, 293618, 293619, 293620, 293621, 293622, 293623, 293624, 293625, 293626, 293627, 293628, 293629, 293631, 293632, 293633, 293634, 293635, 293636, 293637, 293638, 293639, 293640, 293641, 293642, 293643, 293645, 293646, 293647, 293648, 293651, 293652, 293654, 293655, 293656, 293657, 293658, 293659, 293660, 293661, 293662, 293663, 293664, 293665, 293668, 293669, 293670, 293671, 293672, 293673, 293674, 293675, 293676, 293677, 293678, 293679, 293680, 293683, 293684, 293685, 293686, 293687, 293688, 293689, 293690, 293691, 293692, 293693, 293768, 293769, 293770, 293771, 293772, 293773, 293775, 293776, 293777, 293778, 293779, 293780, 293781, 293782, 293788, 293789, 293790, 293791, 293793, 293794, 293795, 293797, 293798, 293800, 293802, 293804, 293805, 293806, 293807, 293808, 293809, 293810, 293811, 293812, 293813, 293814, 293815, 293816, 293817, 293818, 293819, 293820, 293821, 293823, 293824, 293826, 293827, 293828, 293829, 293830, 293832, 293833, 293834, 293835, 293836, 293837, 293838, 293840, 293841, 293842, 293843, 293844, 293845, 293846, 293847, 293851, 293853, 293858, 293859, 293862, 293866, 293867, 293868, 293869, 293870, 293871, 293872, 293873, 293874, 293875, 293876, 293877, 293878, 293879, 293880, 293881, 293882, 293883, 293884, 293885, 293886, 293887, 293888, 293890, 293891, 293892, 293893, 293895, 293896, 293897, 293898, 293899, 293900, 293901, 293902, 293903, 293904, 293906, 293907, 293908, 293909, 293910, 293911, 293912, 293913, 293914, 293915, 293916, 293917, 293918, 293919, 293920, 293921, 293922, 293923, 293924, 293925, 293926, 293927, 293928, 293929, 293930, 293931, 293932, 293933, 293934, 293935, 293936, 293937, 293938, 293939, 293940, 293941, 293942, 293943, 293944, 293945, 293946, 293947, 293949, 293950, 293951, 293953, 293954, 293955, 293956, 293957, 293958, 293959, 293960, 293961, 293962, 293963, 293964, 293965, 293966, 293968, 293969, 293970, 293971, 293972, 293973, 293974, 293975, 293978, 293991, 293992, 294012, 294013, 294023, 294024, 294048, 294049, 294050, 294051, 294053, 294054, 294056, 294057, 294058, 294059, 294060, 294061, 294062, 294063, 294064, 294084, 294093, 294105, 294119, 294121, 294125, 294126, 294350, 294351, 294352, 294353, 294362, 294363, 294364, 294366, 294367, 294368, 294369, 294370, 294371, 294372, 294373, 294374, 294377, 294378, 294380, 294381, 294387, 294388, 294389, 294390, 294391, 294393, 294394, 294395, 294396, 294397, 294399, 294400, 294401, 294402, 294403, 294405, 294406, 294407, 294409, 294410, 294411, 294413, 294414, 294415, 294416, 294417, 294418, 294419, 294420, 294421, 294422, 294423, 294425, 294426, 294427, 294428, 294429, 294430, 294431, 294432, 294433, 294435, 294439, 294440, 294441, 294442, 294443, 294445, 294446, 294447, 294448, 294449, 294450, 294451, 294452, 294453, 294454, 294455, 294456, 294457, 294458, 294459, 294460, 294461, 294463, 294464, 294465, 294466, 294468, 294469, 294472, 294474, 294478, 294479, 294480, 294482, 294483, 294484, 294486, 294487, 294488, 294489, 294490, 294491, 294492, 294493, 294494, 294495, 294496, 294499, 294501, 294502, 294503, 294504, 294506, 294507, 294508, 294509, 294510, 294511, 294514, 294515, 294516, 294517, 294518, 294519, 294520, 294521, 294522, 294523, 294525, 294526, 294527, 294529, 294530, 294531, 294532, 294533, 294534, 294535, 294536, 294537, 294538, 294539, 294540, 294541, 294542, 294543, 294544, 294545, 294546, 294547, 294548, 294582, 294651, 294778, 294779, 294780, 294781, 294782, 294783, 294785, 294786, 294787, 294788, 294789, 294790, 294791, 294792, 294793, 294795, 294796, 294797, 294798, 294799, 294812, 294813, 294814, 294815, 294816, 294817, 294818, 294819, 294820, 294821, 294822, 294823, 294824, 294825, 294826, 294827, 294828, 294829, 294830, 294831, 294832, 294833, 294834, 294835, 294836,294837, 294838, 294839, 294841, 294842, 294844, 294846, 294848, 294849, 294850, 294853, 294854, 294855, 294856, 294858, 294859, 294860, 294861, 294863, 294864, 294865, 294866, 294867, 294868, 294869, 294870, 294871, 294872, 294873, 294874, 294876, 294877, 294878, 294880, 294881, 294883, 294884, 294885, 294886, 294887, 294888, 294889, 294891, 294892, 294893, 294895, 294896, 294897, 294898, 294899, 294900, 294901, 294902, 294903, 294904, 294905, 294906, 294907, 294908, 294909, 294910, 294911, 294912, 294913, 294914, 294915, 294916, 294917, 294918, 294919, 294920, 294921, 294922, 294923, 294924, 294925, 294926, 294952, 294953, 294954, 294955, 294956, 294957, 294958, 294959, 294960, 294961, 294962, 294963, 294964, 294965, 294966, 294967, 294968, 294969, 294970, 294971, 294972, 294973, 294974, 294975, 294976, 294977, 294978, 294979, 294980, 294981, 294982, 294983, 294984, 294985, 294986, 294987, 294989, 294990, 294991, 294992, 294993, 294994, 294995, 294996, 294997, 294998, 295000, 295001, 295002, 295003, 295004, 295006, 295007, 295008, 295009, 295010, 295011, 295012, 295013, 295014, 295015, 295016, 295017, 295019, 295024, 295025, 295026, 295027, 295028, 295029, 295031, 295032, 295033, 295034, 295035, 295036, 295037, 295038, 295040, 295041, 295042, 295043, 295044, 295045, 295046, 295047, 295048, 295049, 295051, 295052, 295054, 295055, 295057, 295058, 295059, 295060, 295061, 295063, 295064, 295065, 295066, 295067, 295068, 295069, 295070, 295071, 295072, 295073, 295074, 295076, 295077, 295079, 295081, 295082, 295083, 295084, 295085, 295086, 295087, 295089, 295090, 295091, 295092, 295094, 295095, 295096, 295097, 295099, 295101, 295102, 295104, 295105, 295106, 295107, 295108, 295109, 295110, 295111, 295112, 295113, 295114, 295115, 295116, 295117, 295118, 295119, 295120, 295122, 295123, 295124, 295125, 295126, 295127, 295128, 295129, 295130, 295131, 295132, 295133, 295134, 295135, 295136, 295137, 295138, 295139, 295140, 295142, 295144, 295145, 295146, 295147, 295148, 295149, 295150, 295151, 295152, 295153, 295154, 295155, 295156, 295157, 295158, 295159, 295160, 295161, 295162, 295163, 295164, 295165, 295166, 295167, 295169, 295170, 295171, 295172, 295176, 295177, 295178, 295179, 295180, 295181, 295182, 295183, 295184, 295185, 295186, 295187, 295188, 295189, 295190, 295191, 295192, 295193, 295194, 295195, 295196, 295197, 295198, 295199, 295200, 295201, 295202, 295203, 295204, 295205, 295206, 295208, 295209, 295210, 295211, 295213, 295214, 295215, 295216, 295217, 295218, 295219, 295220, 295221, 295223, 295224, 295225, 295226, 295227, 295229, 295230, 295231, 295232, 295233, 295234, 295235, 295236, 295239, 295240, 295241, 295242, 295243, 295244, 295245, 295246, 295247, 295248, 295250, 295252, 295253, 295254, 295255, 295256, 295257, 295258, 295259, 295260, 295261, 295262, 295263, 295264, 295266, 295267, 295269, 295270, 295271, 295272, 295273, 295274, 295275, 295276, 295277, 295278, 295279, 295280, 295282, 295283, 295284, 295286, 295287, 295288, 295289, 295290, 295291, 295292, 295293, 295295, 295296, 295297, 295298, 295299, 295300, 295301, 295302, 295303, 295304, 295305, 295306, 295307, 295308, 295309, 295310, 295311, 295312, 295314, 295315, 295316, 295318, 295320, 295321, 295322, 295326, 295328, 295329, 295330, 295331, 295332, 295333, 295334, 295335, 295336, 295337, 295338, 295339, 295340, 295341, 295342, 295343, 295344, 295345, 295346, 295347, 295348, 295349, 295350, 295351, 295352, 295353, 295355, 295356, 295357, 295358, 295359, 295361, 295362, 295363, 295364, 295366, 295367, 295369, 295370, 295371, 295374, 295375, 295377, 295378, 295380, 295382, 295384, 295385, 295387, 295390, 295392, 295393, 295395, 295396, 295397, 295403, 295405, 295406, 295407, 295408, 295410, 295412, 295413, 295415, 295416, 295419, 295421, 295422, 295424, 295425, 295429, 295430, 295431, 295435, 295440, 295441, 295442, 295443, 295445, 295446, 295448, 295450, 295451, 295452, 295453, 295454, 295455, 295458, 295459, 295460, 295461, 295462, 295463, 295465, 295466, 295468, 295470, 295472, 295473, 295474, 295475, 295476, 295477, 295478, 295480, 295481' --, 295483, 295484, 295486, 295487, 295488, 295489, 295490, 295491, 295492, 295493, 295495, 295496, 295499, 295500, 295501, 295502, 295503, 295504, 295505, 295506, 295507, 295508, 295509, 295511, 295512, 295513, 295514, 295515, 295516, 295517, 295518, 295519, 295520, 295521, 295522, 295525, 295526, 295527, 295528, 295529, 295530, 295533, 295534, 295535, 295536, 295537, 295538, 295539, 295540, 295541, 295542, 295543, 295544, 295545, 295546, 295547, 295548, 295550, 295554, 295555, 295556, 295557, 295558, 295559, 295560, 295561, 295562, 295563, 295564, 295565, 295567, 295568, 295569, 295570, 295571, 295572, 295573, 295574, 295575, 295576, 295577, 295578, 295580, 295581, 295582, 295583, 295584, 295585, 295586, 295587, 295589, 295592, 295594, 295595, 295596, 295597, 295599, 295600, 295601, 295602, 295603, 295604, 295605, 295606, 295607, 295608, 295609, 295610, 295611, 295612, 295616, 295618, 295619, 295620, 295621, 295622, 295623, 295625, 295626, 295627, 295628, 295629, 295630, 295631, 295632, 295633, 295634, 295635, 295636, 295637, 295638, 295641, 295642, 295643, 295644, 295645, 295646, 295647, 295648, 295649, 295650, 295651, 295652, 295653, 295654, 295656, 295657, 295660, 295662, 295664, 295666, 295667, 295668, 295670, 295671, 295672, 295673, 295674, 295675, 295676, 295677, 295678, 295679, 295680, 295681, 295682, 295683, 295684, 295685, 295686, 295687, 295688, 295689, 295691, 295692, 295693, 295694, 295695, 295696, 295697, 295698, 295699, 295700, 295701, 295702, 295703, 295704, 295705, 295706, 295707, 295708, 295710, 295711, 295712, 295713, 295714, 295715, 295716, 295717, 295718, 295719, 295720, 295721, 295722, 295723, 295724, 295725, 295726, 295727, 295728, 295729, 295730, 295731, 295732, 295733, 295734, 295735, 295736, 295737, 295738, 295740, 295742, 295743, 295744, 295746, 295747, 295748, 295749, 295750, 295753, 295760, 295762, 295766, 295767, 295771, 295772, 295775, 295776, 295778, 295779, 295780, 295781, 295782, 295784, 295785, 295787, 295791, 295792, 295793, 295794, 295795, 295796, 295797, 295798, 295800, 295801, 295802, 295803, 295804, 295806, 295807, 295808, 295809, 295813,'
295814, 295815, 295816, 295817, 295819, 295820, 295822, 295823, 295824, 295825, 295826, 295827, 295828, 295829, 295830, 295831, 295832, 295833, 295834, 295835, 295837, 295838, 295839, 295840, 295842, 295844, 295845, 295846, 295847, 295848, 295849, 295850, 295851, 295852, 295853, 295854, 295855, 295856, 295857, 295858, 295859, 295860, 295861, 295862, 295863, 295864, 295865, 295866, 295867, 295868, 295869, 295870, 295871, 295872, 295874, 295875, 295876, 295877, 295878, 295879, 295880, 295881, 295882, 295883, 295884, 295885, 295886, 295887, 295888, 295889, 295891, 295892, 295893, 295894, 295895, 295896'
--select @holdoff

declare @holdoffs table (
row int
,val int
)


insert into @holdoffs
select id, CAST(val as int) from dbo.ParseValues(@holdoff,',')


select * from @holdoffs

webfred
Flowing Fount of Yak Knowledge

Germany
8760 Posts

Posted - 09/27/2012 :  05:34:38  Show Profile  Visit webfred's Homepage  Reply with Quote
quote:
Originally posted by esthera

it seems my nvarchar max is cutting

see the following code - it always shows only 1001



declare @holdoff nvarchar(max)
select @holdoff='282990, 282991, 283012, 283014, 283016, 283017, 283018, 283019, 283020, 283021, 283022, 283023, 283024, 283025, 283026, 283027, 283028, 283029, 283031, 283034, 286574, 287154, 287240, 287272, 287457, 287690, 287732, 287744, 287843, 287966, 288227, 288228, 288443, 288842, 288938, 289010, 289339, 289709, 289987, 290101, 290107, 290108, 290238, 290251, 291682, 291735, 291803, 291826, 291922, 291952, 291966, 291979, 292073, 292568, 292618, 292626, 292679, 292715, 292800, 292817, 292835, 292836, 292837, 292838, 292840, 292842, 292843, 292844, 292845, 292846, 292847, 292848, 292849, 292850, 292851, 292852, 292853, 292854, 292855, 292856, 292857, 292858, 292859, 292860, 292861, 292862, 292863, 292864, 292865, 292866, 292867, 292868, 292869, 292870, 292871, 292873, 292874, 292875, 292876, 292894, 292895, 292897, 292898, 292899, 292900, 292901, 292902, 292903, 292904, 292905, 292906, 292908, 292909, 292912, 292913, 292914, 292915, 292916, 292918, 292919, 292920, 292921, 292922, 292923, 292924, 292925, 292926, 292927, 292936, 292938, 292939, 292942, 292943, 292944, 292945, 292946, 292955, 292966, 292980, 293099, 293100, 293101, 293102, 293103, 293104, 293105, 293106, 293107, 293108, 293109, 293110, 293111, 293112, 293113, 293114, 293115, 293116, 293117, 293118, 293119, 293120, 293121, 293122, 293123, 293124, 293125, 293126, 293127, 293149, 293150, 293154, 293155, 293156, 293157, 293158, 293181, 293182, 293189, 293193, 293194, 293196, 293197, 293198, 293199, 293200, 293202, 293210, 293214, 293216, 293217, 293218, 293219, 293220, 293221, 293226, 293230, 293231, 293232, 293233, 293238, 293239, 293241, 293242, 293243, 293244, 293245, 293246, 293247, 293248, 293249, 293250, 293251, 293252, 293253, 293254, 293255, 293256, 293257, 293258, 293297, 293301, 293308, 293309, 293310, 293311, 293312, 293313, 293314, 293315, 293316, 293317, 293318, 293319, 293320, 293321, 293322, 293323, 293324, 293325, 293326, 293327, 293328, 293344, 293345, 293346, 293347, 293348, 293349, 293351, 293352, 293353, 293354, 293355, 293356, 293357, 293369, 293381, 293382, 293383, 293384, 293385, 293386, 293387, 293388, 293389, 293390, 293391, 293392, 293393, 293395, 293396, 293397, 293398, 293399, 293400, 293401, 293403, 293406, 293407, 293408, 293411, 293412, 293413, 293414, 293415, 293416, 293417, 293418, 293419, 293420, 293421, 293422, 293423, 293428, 293429, 293430, 293431, 293433, 293439, 293441, 293442, 293444, 293445, 293447, 293464, 293465, 293466, 293467, 293468, 293469, 293470, 293471, 293472, 293473, 293474, 293475, 293476, 293477, 293478, 293480, 293481, 293482, 293483, 293484, 293485, 293486, 293487, 293488, 293489, 293490, 293491, 293496, 293497, 293498, 293499, 293501, 293502, 293503, 293504, 293505, 293506, 293507, 293508, 293509, 293510, 293511, 293512, 293513, 293514, 293515, 293516, 293517, 293518, 293553, 293554, 293555, 293565, 293566, 293567, 293568, 293569, 293570, 293572, 293573, 293574, 293575, 293577, 293578, 293579, 293583, 293584, 293586, 293587, 293589, 293591, 293592, 293593, 293594, 293597, 293598, 293602, 293603, 293604, 293605, 293606, 293607, 293608, 293609, 293610, 293611, 293612, 293613, 293614, 293615, 293616, 293617, 293618, 293619, 293620, 293621, 293622, 293623, 293624, 293625, 293626, 293627, 293628, 293629, 293631, 293632, 293633, 293634, 293635, 293636, 293637, 293638, 293639, 293640, 293641, 293642, 293643, 293645, 293646, 293647, 293648, 293651, 293652, 293654, 293655, 293656, 293657, 293658, 293659, 293660, 293661, 293662, 293663, 293664, 293665, 293668, 293669, 293670, 293671, 293672, 293673, 293674, 293675, 293676, 293677, 293678, 293679, 293680, 293683, 293684, 293685, 293686, 293687, 293688, 293689, 293690, 293691, 293692, 293693, 293768, 293769, 293770, 293771, 293772, 293773, 293775, 293776, 293777, 293778, 293779, 293780, 293781, 293782, 293788, 293789, 293790, 293791, 293793, 293794, 293795, 293797, 293798, 293800, 293802, 293804, 293805, 293806, 293807, 293808, 293809, 293810, 293811, 293812, 293813, 293814, 293815, 293816, 293817, 293818, 293819, 293820, 293821, 293823, 293824, 293826, 293827, 293828, 293829, 293830, 293832, 293833, 293834, 293835, 293836, 293837, 293838, 293840, 293841, 293842, 293843, 293844, 293845, 293846, 293847, 293851, 293853, 293858, 293859, 293862, 293866, 293867, 293868, 293869, 293870, 293871, 293872, 293873, 293874, 293875, 293876, 293877, 293878, 293879, 293880, 293881, 293882, 293883, 293884, 293885, 293886, 293887, 293888, 293890, 293891, 293892, 293893, 293895, 293896, 293897, 293898, 293899, 293900, 293901, 293902, 293903, 293904, 293906, 293907, 293908, 293909, 293910, 293911, 293912, 293913, 293914, 293915, 293916, 293917, 293918, 293919, 293920, 293921, 293922, 293923, 293924, 293925, 293926, 293927, 293928, 293929, 293930, 293931, 293932, 293933, 293934, 293935, 293936, 293937, 293938, 293939, 293940, 293941, 293942, 293943, 293944, 293945, 293946, 293947, 293949, 293950, 293951, 293953, 293954, 293955, 293956, 293957, 293958, 293959, 293960, 293961, 293962, 293963, 293964, 293965, 293966, 293968, 293969, 293970, 293971, 293972, 293973, 293974, 293975, 293978, 293991, 293992, 294012, 294013, 294023, 294024, 294048, 294049, 294050, 294051, 294053, 294054, 294056, 294057, 294058, 294059, 294060, 294061, 294062, 294063, 294064, 294084, 294093, 294105, 294119, 294121, 294125, 294126, 294350, 294351, 294352, 294353, 294362, 294363, 294364, 294366, 294367, 294368, 294369, 294370, 294371, 294372, 294373, 294374, 294377, 294378, 294380, 294381, 294387, 294388, 294389, 294390, 294391, 294393, 294394, 294395, 294396, 294397, 294399, 294400, 294401, 294402, 294403, 294405, 294406, 294407, 294409, 294410, 294411, 294413, 294414, 294415, 294416, 294417, 294418, 294419, 294420, 294421, 294422, 294423, 294425, 294426, 294427, 294428, 294429, 294430, 294431, 294432, 294433, 294435, 294439, 294440, 294441, 294442, 294443, 294445, 294446, 294447, 294448, 294449, 294450, 294451, 294452, 294453, 294454, 294455, 294456, 294457, 294458, 294459, 294460, 294461, 294463, 294464, 294465, 294466, 294468, 294469, 294472, 294474, 294478, 294479, 294480, 294482, 294483, 294484, 294486, 294487, 294488, 294489, 294490, 294491, 294492, 294493, 294494, 294495, 294496, 294499, 294501, 294502, 294503, 294504, 294506, 294507, 294508, 294509, 294510, 294511, 294514, 294515, 294516, 294517, 294518, 294519, 294520, 294521, 294522, 294523, 294525, 294526, 294527, 294529, 294530, 294531, 294532, 294533, 294534, 294535, 294536, 294537, 294538, 294539, 294540, 294541, 294542, 294543, 294544, 294545, 294546, 294547, 294548, 294582, 294651, 294778, 294779, 294780, 294781, 294782, 294783, 294785, 294786, 294787, 294788, 294789, 294790, 294791, 294792, 294793, 294795, 294796, 294797, 294798, 294799, 294812, 294813, 294814, 294815, 294816, 294817, 294818, 294819, 294820, 294821, 294822, 294823, 294824, 294825, 294826, 294827, 294828, 294829, 294830, 294831, 294832, 294833, 294834, 294835, 294836,294837, 294838, 294839, 294841, 294842, 294844, 294846, 294848, 294849, 294850, 294853, 294854, 294855, 294856, 294858, 294859, 294860, 294861, 294863, 294864, 294865, 294866, 294867, 294868, 294869, 294870, 294871, 294872, 294873, 294874, 294876, 294877, 294878, 294880, 294881, 294883, 294884, 294885, 294886, 294887, 294888, 294889, 294891, 294892, 294893, 294895, 294896, 294897, 294898, 294899, 294900, 294901, 294902, 294903, 294904, 294905, 294906, 294907, 294908, 294909, 294910, 294911, 294912, 294913, 294914, 294915, 294916, 294917, 294918, 294919, 294920, 294921, 294922, 294923, 294924, 294925, 294926, 294952, 294953, 294954, 294955, 294956, 294957, 294958, 294959, 294960, 294961, 294962, 294963, 294964, 294965, 294966, 294967, 294968, 294969, 294970, 294971, 294972, 294973, 294974, 294975, 294976, 294977, 294978, 294979, 294980, 294981, 294982, 294983, 294984, 294985, 294986, 294987, 294989, 294990, 294991, 294992, 294993, 294994, 294995, 294996, 294997, 294998, 295000, 295001, 295002, 295003, 295004, 295006, 295007, 295008, 295009, 295010, 295011, 295012, 295013, 295014, 295015, 295016, 295017, 295019, 295024, 295025, 295026, 295027, 295028, 295029, 295031, 295032, 295033, 295034, 295035, 295036, 295037, 295038, 295040, 295041, 295042, 295043, 295044, 295045, 295046, 295047, 295048, 295049, 295051, 295052, 295054, 295055, 295057, 295058, 295059, 295060, 295061, 295063, 295064, 295065, 295066, 295067, 295068, 295069, 295070, 295071, 295072, 295073, 295074, 295076, 295077, 295079, 295081, 295082, 295083, 295084, 295085, 295086, 295087, 295089, 295090, 295091, 295092, 295094, 295095, 295096, 295097, 295099, 295101, 295102, 295104, 295105, 295106, 295107, 295108, 295109, 295110, 295111, 295112, 295113, 295114, 295115, 295116, 295117, 295118, 295119, 295120, 295122, 295123, 295124, 295125, 295126, 295127, 295128, 295129, 295130, 295131, 295132, 295133, 295134, 295135, 295136, 295137, 295138, 295139, 295140, 295142, 295144, 295145, 295146, 295147, 295148, 295149, 295150, 295151, 295152, 295153, 295154, 295155, 295156, 295157, 295158, 295159, 295160, 295161, 295162, 295163, 295164, 295165, 295166, 295167, 295169, 295170, 295171, 295172, 295176, 295177, 295178, 295179, 295180, 295181, 295182, 295183, 295184, 295185, 295186, 295187, 295188, 295189, 295190, 295191, 295192, 295193, 295194, 295195, 295196, 295197, 295198, 295199, 295200, 295201, 295202, 295203, 295204, 295205, 295206, 295208, 295209, 295210, 295211, 295213, 295214, 295215, 295216, 295217, 295218, 295219, 295220, 295221, 295223, 295224, 295225, 295226, 295227, 295229, 295230, 295231, 295232, 295233, 295234, 295235, 295236, 295239, 295240, 295241, 295242, 295243, 295244, 295245, 295246, 295247, 295248, 295250, 295252, 295253, 295254, 295255, 295256, 295257, 295258, 295259, 295260, 295261, 295262, 295263, 295264, 295266, 295267, 295269, 295270, 295271, 295272, 295273, 295274, 295275, 295276, 295277, 295278, 295279, 295280, 295282, 295283, 295284, 295286, 295287, 295288, 295289, 295290, 295291, 295292, 295293, 295295, 295296, 295297, 295298, 295299, 295300, 295301, 295302, 295303, 295304, 295305, 295306, 295307, 295308, 295309, 295310, 295311, 295312, 295314, 295315, 295316, 295318, 295320, 295321, 295322, 295326, 295328, 295329, 295330, 295331, 295332, 295333, 295334, 295335, 295336, 295337, 295338, 295339, 295340, 295341, 295342, 295343, 295344, 295345, 295346, 295347, 295348, 295349, 295350, 295351, 295352, 295353, 295355, 295356, 295357, 295358, 295359, 295361, 295362, 295363, 295364, 295366, 295367, 295369, 295370, 295371, 295374, 295375, 295377, 295378, 295380, 295382, 295384, 295385, 295387, 295390, 295392, 295393, 295395, 295396, 295397, 295403, 295405, 295406, 295407, 295408, 295410, 295412, 295413, 295415, 295416, 295419, 295421, 295422, 295424, 295425, 295429, 295430, 295431, 295435, 295440, 295441, 295442, 295443, 295445, 295446, 295448, 295450, 295451, 295452, 295453, 295454, 295455, 295458, 295459, 295460, 295461, 295462, 295463, 295465, 295466, 295468, 295470, 295472, 295473, 295474, 295475, 295476, 295477, 295478, 295480, 295481' --, 295483, 295484, 295486, 295487, 295488, 295489, 295490, 295491, 295492, 295493, 295495, 295496, 295499, 295500, 295501, 295502, 295503, 295504, 295505, 295506, 295507, 295508, 295509, 295511, 295512, 295513, 295514, 295515, 295516, 295517, 295518, 295519, 295520, 295521, 295522, 295525, 295526, 295527, 295528, 295529, 295530, 295533, 295534, 295535, 295536, 295537, 295538, 295539, 295540, 295541, 295542, 295543, 295544, 295545, 295546, 295547, 295548, 295550, 295554, 295555, 295556, 295557, 295558, 295559, 295560, 295561, 295562, 295563, 295564, 295565, 295567, 295568, 295569, 295570, 295571, 295572, 295573, 295574, 295575, 295576, 295577, 295578, 295580, 295581, 295582, 295583, 295584, 295585, 295586, 295587, 295589, 295592, 295594, 295595, 295596, 295597, 295599, 295600, 295601, 295602, 295603, 295604, 295605, 295606, 295607, 295608, 295609, 295610, 295611, 295612, 295616, 295618, 295619, 295620, 295621, 295622, 295623, 295625, 295626, 295627, 295628, 295629, 295630, 295631, 295632, 295633, 295634, 295635, 295636, 295637, 295638, 295641, 295642, 295643, 295644, 295645, 295646, 295647, 295648, 295649, 295650, 295651, 295652, 295653, 295654, 295656, 295657, 295660, 295662, 295664, 295666, 295667, 295668, 295670, 295671, 295672, 295673, 295674, 295675, 295676, 295677, 295678, 295679, 295680, 295681, 295682, 295683, 295684, 295685, 295686, 295687, 295688, 295689, 295691, 295692, 295693, 295694, 295695, 295696, 295697, 295698, 295699, 295700, 295701, 295702, 295703, 295704, 295705, 295706, 295707, 295708, 295710, 295711, 295712, 295713, 295714, 295715, 295716, 295717, 295718, 295719, 295720, 295721, 295722, 295723, 295724, 295725, 295726, 295727, 295728, 295729, 295730, 295731, 295732, 295733, 295734, 295735, 295736, 295737, 295738, 295740, 295742, 295743, 295744, 295746, 295747, 295748, 295749, 295750, 295753, 295760, 295762, 295766, 295767, 295771, 295772, 295775, 295776, 295778, 295779, 295780, 295781, 295782, 295784, 295785, 295787, 295791, 295792, 295793, 295794, 295795, 295796, 295797, 295798, 295800, 295801, 295802, 295803, 295804, 295806, 295807, 295808, 295809, 295813,'
295814, 295815, 295816, 295817, 295819, 295820, 295822, 295823, 295824, 295825, 295826, 295827, 295828, 295829, 295830, 295831, 295832, 295833, 295834, 295835, 295837, 295838, 295839, 295840, 295842, 295844, 295845, 295846, 295847, 295848, 295849, 295850, 295851, 295852, 295853, 295854, 295855, 295856, 295857, 295858, 295859, 295860, 295861, 295862, 295863, 295864, 295865, 295866, 295867, 295868, 295869, 295870, 295871, 295872, 295874, 295875, 295876, 295877, 295878, 295879, 295880, 295881, 295882, 295883, 295884, 295885, 295886, 295887, 295888, 295889, 295891, 295892, 295893, 295894, 295895, 295896'

--select @holdoff

declare @holdoffs table (
row int
,val int
)


insert into @holdoffs
select id, CAST(val as int) from dbo.ParseValues(@holdoff,',')


select * from @holdoffs


The red part isn't moved into the variable...
Also we need to see the code of your function ParseValues please.


Too old to Rock'n'Roll too young to die.
Go to Top of Page

esthera
Flowing Fount of Yak Knowledge

1388 Posts

Posted - 09/27/2012 :  05:51:21  Show Profile  Reply with Quote
you can see the red was just testing if you uncomment it still returns the same amount of rows

here is the parsevalues function


create FUNCTION [dbo].[ParseValues]
(@String varchar(8000), @Delimiter varchar(10) )
RETURNS @RESULTS TABLE (ID int identity(1,1), Val varchar(50))
AS
BEGIN
DECLARE @Value varchar(100)
WHILE @String is not null
BEGIN
SELECT @Value=CASE WHEN PATINDEX('%'+@Delimiter+'%',@String) >0 THEN LEFT(@String,PATINDEX('%'+@Delimiter+'%',@String)-1) ELSE @String END, @String=CASE WHEN PATINDEX('%'+@Delimiter+'%',@String) >0 THEN SUBSTRING(@String,PATINDEX('%'+@Delimiter+'%',@String)+LEN(@Delimiter),LEN(@String)) ELSE NULL END
INSERT INTO @RESULTS (Val)
SELECT @Value
END
RETURN
END
GO

Go to Top of Page

senthil_nagore
Flowing Fount of Yak Knowledge

India
1007 Posts

Posted - 09/27/2012 :  06:17:16  Show Profile  Visit senthil_nagore's Homepage  Send senthil_nagore a Yahoo! Message  Reply with Quote
Your ParseValues () function can able to process 8000 charcters. since you decalre the inpur parameter @String varchar(8000)


Beyon 8000 it will truncate.

Senthil Kumar C
------------------------------------------------------
MCITP - Database Administration SQL SERVER 2008
MCTS - Database Development SQL SERVER 2008

Edited by - senthil_nagore on 09/27/2012 06:18:03
Go to Top of Page

esthera
Flowing Fount of Yak Knowledge

1388 Posts

Posted - 09/27/2012 :  09:07:07  Show Profile  Reply with Quote
thanks for catching that
Go to Top of Page

senthil_nagore
Flowing Fount of Yak Knowledge

India
1007 Posts

Posted - 09/28/2012 :  04:53:42  Show Profile  Visit senthil_nagore's Homepage  Send senthil_nagore a Yahoo! Message  Reply with Quote
you are welcome

Senthil Kumar C
------------------------------------------------------
MCITP - Database Administration SQL SERVER 2008
MCTS - Database Development SQL SERVER 2008
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.47 seconds. Powered By: Snitz Forums 2000