how to speed up loop sql server 2005

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

how to speed up loop sql server 2005

Author

Topic

collie
Constraint Violating Yak Guru

400 Posts

Posted - 2010-04-26 : 10:34:46

Hi,

I receive parameters with values separated by comma such as 123,678,90.
I need to insert the values into a temp table as i need to do some data manipulation later.
temp table
123
678
90

However my query takes too long (over 13 minutes -closed it in the middle as no patience waiting :) ) and i want to know how to rewrite it so it will perform faster.

A sample of the values passed is included in the code below.

Thanks

declare @newValue  nvarchar (50)
  declare @ListCartonId  nvarchar (max) --delimited id string
declare @ListPacklistID  nvarchar (max) --delimited id string
 

declare @err nvarchar (500)
declare    @UserId int
declare @notice_id int
declare @NoPlnotice_id int  
declare @NoCartonNotice_id int  
declare @PODnotice_id int 
DECLARE @Partial nvarchar (max)
declare @count int
set @UserId=99
   declare @del nvarchar (10) --delimiter
 declare @nextstr nvarchar(4000)
DECLARE @nextstrPL  nvarchar(4000)
 declare @pos int
declare @posPL int
 declare @nextpos int
 declare @nextposPL int
declare @statusId int
declare @message nvarchar (100)
DECLARE @PODmessage nvarchar (100)
declare @NoPlMessage nvarchar (50)
declare @NoCartonMessage nvarchar (50)

set @newValue = N'pod' 
	set	@ListCartonId = N'0000091204001744483,0000091204001744484,0000091204001744485,0000091204001744491,0000091204001744492,0000091204001744493,0000091204001744494,0000091204001744495,0000091204002790095,0000091204002790096,0000091204002790097,0000091204002790098,0000091204002790101,0000091204002790102,0000091204002790103,0000091204002790104,0000091204002790105,0000091204002790107,0000091204002790108,0000091204002790109,0000091204002790111,0000091204002790112,0000091204002790113,0000091204011238629,0000091204011238630,0000091204011240000,0000091204011240001,0000091204011240002,0000091204011240003,0000091204011240004,0000091204011240005,0000091204011900421,0000091204011900422,0000091204011902744,0000091204011902745,0000091204011902746,0000091204011902747,0000091204011986914,0000091204011986915,0000091204011986916,0000091204011986917,0000091204011986918,0000091204011986919,0000091204011986920,0000091204011986974,0000091204011986975,0000091204011986976,0000091204011986977,0000091204011986978,0000091204011992033,0000091204011992034,0000091204011992035,0000091204011992036,0000091204011992037,0000091204011992038,0000091204011992039,0000091204011992040,0000091204012028056,0000091204012028057,0000091204012028058,0000091204012033833,0000091204012033834,0000091204012145857,0000091204012355411,0000091204012758098,0000091204012758099,0000091204012758100,0000091204012758101,0000091204012758102,0000091204012758103,0000091204012758104,0000091204012758105,0000091204012758106,0000091204012758107,0000091204012758108,0000091204012758109,0000091204012758110,0000091204012853019,0000091204012853020,0000091204012853021,0000091204012853022,0000091204012853023,0000091204012853024,0000091204012853025,0000091204012853272,0000091204012853273,0000091204012853274,0000091204012853708,0000091204012853709,0000091204012853768,0000091204012853769,0000091204012853782,0000091204012853783,0000091204012853784,0000091204012853785,0000091204012853786,0000091204012853787,0000091204012853788,0000091204012853789,0000091204012853790,0000091204012853795,0000091204012853796,0000091204012853797,0000091204012853798,0000091204012853799,0000091204012853800,0000091204012853801,0000091204012853802,0000091204012853803,0000091204012853804,0000091204012853805,0000091204012853806,0000091204012853807,0000091204012853808,0000091204012853809,0000091204012853839,0000091204012853842,0000091204012853843,0000091204012853844,0000091204012853845,0000091204012853846,0000091204012853847,0000091204012853848,0000091204012853849,0000091204012853850,0000091204012853851,0000091204012853852,0000091204012853853,0000091204012853854,0000091204012853855,0000091204012853856,0000091204012853857,0000091204012853858,0000091204012853859,0000091204012853860,0000091204012853861,0000091204012853862,0000091204012853863,0000091204012853864,0000091204012853865,0000091204012853895,0000091204012853896,0000091204012853897,0000091204012853898,0000091204012853910,0000091204012853911,0000091204012853912,0000091204012853913,0000091204012853914,0000091204012853915,0000091204012853916,0000091204012853917,0000091204012853918,0000091204012853919,0000091204012853920,0000091204012853922,0000091204012853923,0000091204012853924,0000091204012853925,0000091204012853926,0000091204012853938,0000091204012853939,0000091204012853940,0000091204012853941,0000091204012853942,0000091204012853943,0000091204012853944,0000091204012853945,0000091204012853946,0000091204012853947,0000091204012853948,0000091204012853949,0000091204012853950,0000091204012853951,0000091204012853952,0000091204012853953,0000091204012853954,0000091204012853955,0000091204012853956,0000091204012853957,0000091204012853958,0000091204012853959,0000091204012853960,0000091204012853961,0000091204012853962,0000091204012853971,0000091204012853972,0000091204012853973,0000091204012853974,0000091204012853975,0000091204012853976,0000091204012853977,0000091204012853978,0000091204012853979,0000091204012853980,0000091204012853981,0000091204012854095,0000091204012854097,0000091204012854098,0000091204012854099,0000091204012854100,0000091204012854101,0000091204012854102,0000091204012854103,0000091204012854115,0000091204012854525,0000091204012854526,0000091204012854527,0000091204012854528,0000091204012854529,0000091204012854530,0000091204012854531,0000091204012854532,0000091204012854533,0000091204012854534,0000091204012854535,0000091204012854536,0000091204012854537,0000091204012854538,0000091204012854539,0000091204012854540,0000091204012854541,0000091204012854542,0000091204012854543,0000091204012854544,0000091204012854545,0000091204012854546,0000091204012854547,0000091204012854548,0000091204012854549,0000091204012854550,0000091204012854551,0000091204012854552,0000091204012854553,0000091204012854554,0000091204012854555,0000091204012854556,0000091204012854557,0000091204012854558,0000091204012854559,0000091204012854560,0000091204012854561,0000091204012854562,0000091204012854563,0000091204012854564,0000091204012854565,0000091204012854566,0000091204012854567,0000091204012854568,0000091204012854569,0000091204012854570,0000091204012854571,0000091204012854572,0000091204012854573,0000091204012854574,0000091204012854575,0000091204012854576,0000091204012854577,0000091204012854578,0000091204012854579,0000091204012854580,0000091204012854581,0000091204012854582,0000091204012854583,0000091204012854584,0000091204012854585,0000091204012854586,0000091204012854587,0000091204012854588,0000091204012854589,0000091204012854590,0000091204012854591,0000091204012854592,0000091204012854593,0000091204012854594,0000091204012854595,0000091204012854596,0000091204012854597,0000091204012854598,0000091204012905303,0000091204012905304,0000091204012905305,0000091204012905313,0000091204012905315,0000091204012905317,0000091204012905319,0000091204012905322,0000091204012905323,0000091204013010717,0000091204013085712,0000091204013087727,0000091204013087732,0000091204013185544,0000091204013185552,0000091204013290297,0000091204013290865,0000091204013290866,0000091204013290867,0000091204013498383,0000091204013498384,0000091204013498385,0000091204013498386,0000091204013498387,0000091204013498388,0000091204013498389,0000091204013498390,0000091204013530057,0000091204013530058,0000091204013530059,0000091204013532882,0000091204013532883,0000091204013532884,0000091204013532885,0000091204013532886,0000091204013532887,0000091204013532888,0000091204013532889,0000091204013532890,0000091204013532891,0000091204013532892,0000091204013532893,0000091204013532894,0000091204013532895,0000091204013532896,0000091204013532897,0000091204013532898,0000091204013532899,0000091204013583372,0000091204013583373,0000091204013583374,0000091204013583375,0000091204013583376,0000091204013638006,0000091204013638007,0000091204013638430,0000091204013638431,0000091204013638432,0000091204013639136,0000091204013639137,0000091204013639138,0000091204013639139,0000091204013639140,0000091204013639141,0000091204013639142,0000091204013639143,0000091204013639144,0000091204013639145,0000091204013639146,0000091204013639147,0000091204013639148,0000091204013639149,0000091204013640589,0000091204013674492,0000091204013674493,0000091204013674494,0000091204013674524,0000091204013674692,0000091204013674694,0000091204013677003,0000091204013677004,0000091204013678469,0000091204013678470,0000091204013678471,0000091204013678472,0000091204013678473,0000091204013678474,0000091204013678475,0000091204013678476,0000091204013678477,0000091204013678478,0000091204013678479,0000091204013678480,0000091204013678481,0000091204013678482,0000091204013678483,0000091204013678484,0000091204013678485,0000091204013737633,0000091204013738188,0000091204013738189,0000091204013738190,0000091204013738191,0000091204013738192,0000091204013738193,0000091204013738194,0000091204013738195,0000091204013738196,0000091204013738197,0000091204013738198,0000091204013756478,0000091204013756479,0000091204013756480,0000091204013756481,0000091204013756482,0000091204013756483,0000091204013756484,0000091204013756485,0000091204013756486,0000091204013756487,0000091204013756488,0000091204013756489,0000091204013756490,0000091204013756491,0000091204013756492,0000091204013756493,0000091204013756494,0000091204013756495,0000091204013756496,0000091204013756497,0000091204013756498,0000091204013756499,0000091204013756500,0000091204013756501,0000091204013756502,0000091204013756503,0000091204013756504,0000091204013850532,0000091204013850561,0000091204013850562,0000091204013850563,0000091204013850807,0000091204013851642,0000091204013851825,0000091204013851826,0000091204013851827,0000091204013851828,0000091204013851829,0000091204013851830,0000091204013851831,0000091204013852335,0000091204013852729,0000091204013852730,0000091204013852731,0000091204013852732,0000091204013852733,0000091204013852734,0000091204013852735,0000091204013852736,0000091204013852737,0000091204013852738,0000091204013852739,0000091204013852740,0000091204013852741,0000091204013852814,0000091204013852815,0000091204013852816,0000091204013852820,0000091204013852821,0000091204013852822,0000091204013852823,0000091204013852824,0000091204013852825,0000091204013852826,0000091204013852827,0000091204013852828,0000091204013852829,0000091204013852830,0000091204013852831,0000091204013852832,0000091204013852833,0000091204013852834,0000091204013852835,0000091204013852836,0000091204013852837,0000091204013852838,0000091204013852839,0000091204013852840,0000091204013852841,0000091204013852842,0000091204013852843,0000091204013852844,0000091204013852845,0000091204013852846,0000091204013852847,0000091204013852848,0000091204013852849,0000091204013852850,0000091204013852851,0000091204013852852,0000091204013852853,0000091204013852916,0000091204013852917,0000091204013852918,0000091204013852919,0000091204013852920,0000091204013852921,0000091204013852922,0000091204013852923,0000091204013852944,0000091204013852945,0000091204013852946,0000091204013853937,0000091204013853938,0000091204013853939,0000091204013853940,0000091204013853941,0000091204013853942,0000091204013853943,0000091204013853944,0000091204013853945'
		set @ListPacklistID = N'13523771,13584158,14033215,14242643,14268033,14488209,14514308,14526419,14730907,14730908,14730909,14880225,14942774,14942775,14942776,14952739,14966165,14986745,14987714,14998226,14998826,15014943,15021152,15021153,15021154,15048162,15048301,15048302,15048303,15048304,15048305,15048306,15048307,15048308,15048309,15048310,15048311,15048312,15048313,15048336,15048337,15048338,15048341,15048342,15048343,15048344,15048345,15048346,15048347,15048348,15048349,15048350,15048351,15048384,15048385,15048386,15048387,15048406,15048709,15048710,15048711,15048712,15048713,15048714,15048715,15048716,15048717,15048718,15048719,15048720,15048721,15048722,15048723,15048724,15048725,15048726,15048780,15048781,15048782,15048812,15048813,15048814,15048860,15048862,15048921,15053597,15053872,15053873,15053874,15053875,15053876,15053877,15053878,15053879,15053880,15053881,15053882,15053883,15053884,15053885,15053886,15053887,15053888,15053889,15053890,15053891,15053892,15053893,15053894,15053895,15053897,15053898,15053899,15059536,15059537,15059616,15059617,15059618,15059619,15059620,15059621,15059622,15059623,15059624,15059625,15059650,15059651,15059652,15059653,15059654,15059655,15059656,15059671,15059672,15059673,15059674,15059675,15059676,15059677,15059678,15059679,15059680,15059681,15059682,15059683,15059684,15059690,15059692,15059693,15059694,15059695,15059696,15059697,15059698,15059699,15059700,15059701,15059702,15059703,15059704,15059705,15059711,15059712,15059713,15059715,15059716,15059717,15059718,15059719,15059720,15059721,15059722,15059727,15059728,15059729,15059730,15059731,15059732,15059733,15059734,15059735,15059736,15059739,15059765,15059766,15059767,15059768,15059769,15059770,15059771,15059794,15059795,15059887,15059888,15059889,15059890,15059891,15059892,15059893'
---split cartons and put in temp table to make it easier to use
 
if object_id('tempdb..#valuetable') is not null

    drop table #valuetable
 create table #valuetable (id int identity,  ListItemsId varchar(500),  PacklistID varchar(500))
set @del=','
 set @nextstr = ''
 set @ListCartonId = @ListCartonId + @del
 set @pos = charindex(@del,@ListCartonId)
 set @nextpos = 1

 set @nextstrpl = ''
 set @ListPacklistID = @ListPacklistID + @del
 set @posPL = charindex(@del,@ListPacklistID)
 set @nextposPL = 1
 while (@pos <>  0)  
 begin
 while (@posPL <>  0)  BEGIN
  set @nextstr = substring(@ListCartonId,1,@pos - 1)
set @nextstrpl = substring(@ListpacklistId,1,@pospl - 1)
 
      insert  into #valuetable 
      ([ListItemsId],PacklistID) 
      values 
      (@nextstr,@nextstrpl)
  set @ListCartonId = substring(@ListCartonId,@pos +1,len(@ListCartonId))
  set @nextpos = @pos
  set @pos  = charindex(@del,@ListCartonId)

 set @ListpacklistId = substring(@ListpacklistId,@posPL +1,len(@ListpacklistId))
  set @nextposPL = @posPL
  set @posPL  = charindex(@del,@ListpacklistId)
 
 
 end
end

Whisky-my beloved dog who died suddenly on the 29/06/06-I miss u so much.

asgast
Posting Yak Master

149 Posts

Posted - 2010-04-26 : 10:47:44

Since you are trying to pass depending values, I'd suggest to pass it in xml format and then query the xml variable to get them out.

Can't fall asleep, the ghost of sql server will get me for writing bad queries. Have to stay awake ...

waterduck
Aged Yak Warrior

982 Posts

Posted - 2010-04-26 : 11:27:12

http://sqlblogcasts.com/blogs/madhivanan/archive/2008/09/11/splitting-delimited-data-to-columns-set-based-approach.aspx
find madhi ^^

Hope can help...but advise to wait pros with confirmation...

collie
Constraint Violating Yak Guru

400 Posts

Posted - 2010-04-27 : 06:02:22

quote:
Originally posted by asgast

Since you are trying to pass depending values, I'd suggest to pass it in xml format and then query the xml variable to get them out.

Can't fall asleep, the ghost of sql server will get me for writing bad queries. Have to stay awake ...

Can you please give me a sample or let me know how to look for it in google?

thanks

Whisky-my beloved dog who died suddenly on the 29/06/06-I miss u so much.

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2010-04-27 : 06:10:27

just make use of either one of these function
CSVTable
fnParseList

KH
[spoiler]Time is always against us[/spoiler]

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-04-27 : 06:14:18

http://visakhm.blogspot.com/2010/02/parsing-delimited-string.html

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

collie
Constraint Violating Yak Guru

400 Posts

Posted - 2010-04-27 : 06:32:18

quote:
Originally posted by visakh16

http://visakhm.blogspot.com/2010/02/parsing-delimited-string.html

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Thanks
How can i use the example with 2 different comma delimited parameters?
I tried loop within loop but it takes a very long time.

Whisky-my beloved dog who died suddenly on the 29/06/06-I miss u so much.

karthik_padbanaban
Constraint Violating Yak Guru

263 Posts

Posted - 2010-04-27 : 06:41:53

I am getting 99500 rows in 4 secs.

for every ListCartonId you will have all pack id right?

Karthik
http://karthik4identity.blogspot.com/

karthik_padbanaban
Constraint Violating Yak Guru

263 Posts

Posted - 2010-04-27 : 06:45:14

--Will this help you? I got the result in 4 secs by using the below script


declare @newValue  nvarchar (50)
  declare @ListCartonId  nvarchar (max) --delimited id string
declare @ListPacklistID  nvarchar (max) --delimited id string
 

declare @err nvarchar (500)
declare  @UserId int
declare @notice_id int
declare @NoPlnotice_id int  
declare @NoCartonNotice_id int  
declare @PODnotice_id int 
DECLARE @Partial nvarchar (max)
declare @count int
set @UserId=99

  declare @del nvarchar (10) --delimiter
 declare @nextstr nvarchar(4000)
DECLARE @nextstrPL  nvarchar(4000)
 declare @pos int
declare @posPL int
 declare @nextpos int
 declare @nextposPL int
declare @statusId int
declare @message nvarchar (100)
DECLARE @PODmessage nvarchar (100)
declare @NoPlMessage nvarchar (50)
declare @NoCartonMessage nvarchar (50)
declare @ListCartonIdIns nvarchar(max)
declare @ListPacklistIDIns nvarchar(max)

declare  @ListCartonIdtbl table(carton varchar(100))
declare  @ListPacklistIDtbl table(pack varchar(100))

set @newValue = N'pod' 
	set	@ListCartonId = N'0000091204001744483,0000091204001744484,0000091204001744485,0000091204001744491,0000091204001744492,0000091204001744493,0000091204001744494,0000091204001744495,0000091204002790095,0000091204002790096,0000091204002790097,0000091204002790098,0000091204002790101,0000091204002790102,0000091204002790103,0000091204002790104,0000091204002790105,0000091204002790107,0000091204002790108,0000091204002790109,0000091204002790111,0000091204002790112,0000091204002790113,0000091204011238629,0000091204011238630,0000091204011240000,0000091204011240001,0000091204011240002,0000091204011240003,0000091204011240004,0000091204011240005,0000091204011900421,0000091204011900422,0000091204011902744,0000091204011902745,0000091204011902746,0000091204011902747,0000091204011986914,0000091204011986915,0000091204011986916,0000091204011986917,0000091204011986918,0000091204011986919,0000091204011986920,0000091204011986974,0000091204011986975,0000091204011986976,0000091204011986977,0000091204011986978,0000091204011992033,0000091204011992034,0000091204011992035,0000091204011992036,0000091204011992037,0000091204011992038,0000091204011992039,0000091204011992040,0000091204012028056,0000091204012028057,0000091204012028058,0000091204012033833,0000091204012033834,0000091204012145857,0000091204012355411,0000091204012758098,0000091204012758099,0000091204012758100,0000091204012758101,0000091204012758102,0000091204012758103,0000091204012758104,0000091204012758105,0000091204012758106,0000091204012758107,0000091204012758108,0000091204012758109,0000091204012758110,0000091204012853019,0000091204012853020,0000091204012853021,0000091204012853022,0000091204012853023,0000091204012853024,0000091204012853025,0000091204012853272,0000091204012853273,0000091204012853274,0000091204012853708,0000091204012853709,0000091204012853768,0000091204012853769,0000091204012853782,0000091204012853783,0000091204012853784,0000091204012853785,0000091204012853786,0000091204012853787,0000091204012853788,0000091204012853789,0000091204012853790,0000091204012853795,0000091204012853796,0000091204012853797,0000091204012853798,0000091204012853799,0000091204012853800,0000091204012853801,0000091204012853802,0000091204012853803,0000091204012853804,0000091204012853805,0000091204012853806,0000091204012853807,0000091204012853808,0000091204012853809,0000091204012853839,0000091204012853842,0000091204012853843,0000091204012853844,0000091204012853845,0000091204012853846,0000091204012853847,0000091204012853848,0000091204012853849,0000091204012853850,0000091204012853851,0000091204012853852,0000091204012853853,0000091204012853854,0000091204012853855,0000091204012853856,0000091204012853857,0000091204012853858,0000091204012853859,0000091204012853860,0000091204012853861,0000091204012853862,0000091204012853863,0000091204012853864,0000091204012853865,0000091204012853895,0000091204012853896,0000091204012853897,0000091204012853898,0000091204012853910,0000091204012853911,0000091204012853912,0000091204012853913,0000091204012853914,0000091204012853915,0000091204012853916,0000091204012853917,0000091204012853918,0000091204012853919,0000091204012853920,0000091204012853922,0000091204012853923,0000091204012853924,0000091204012853925,0000091204012853926,0000091204012853938,0000091204012853939,0000091204012853940,0000091204012853941,0000091204012853942,0000091204012853943,0000091204012853944,0000091204012853945,0000091204012853946,0000091204012853947,0000091204012853948,0000091204012853949,0000091204012853950,0000091204012853951,0000091204012853952,0000091204012853953,0000091204012853954,0000091204012853955,0000091204012853956,0000091204012853957,0000091204012853958,0000091204012853959,0000091204012853960,0000091204012853961,0000091204012853962,0000091204012853971,0000091204012853972,0000091204012853973,0000091204012853974,0000091204012853975,0000091204012853976,0000091204012853977,0000091204012853978,0000091204012853979,0000091204012853980,0000091204012853981,0000091204012854095,0000091204012854097,0000091204012854098,0000091204012854099,0000091204012854100,0000091204012854101,0000091204012854102,0000091204012854103,0000091204012854115,0000091204012854525,0000091204012854526,0000091204012854527,0000091204012854528,0000091204012854529,0000091204012854530,0000091204012854531,0000091204012854532,0000091204012854533,0000091204012854534,0000091204012854535,0000091204012854536,0000091204012854537,0000091204012854538,0000091204012854539,0000091204012854540,0000091204012854541,0000091204012854542,0000091204012854543,0000091204012854544,0000091204012854545,0000091204012854546,0000091204012854547,0000091204012854548,0000091204012854549,0000091204012854550,0000091204012854551,0000091204012854552,0000091204012854553,0000091204012854554,0000091204012854555,0000091204012854556,0000091204012854557,0000091204012854558,0000091204012854559,0000091204012854560,0000091204012854561,0000091204012854562,0000091204012854563,0000091204012854564,0000091204012854565,0000091204012854566,0000091204012854567,0000091204012854568,0000091204012854569,0000091204012854570,0000091204012854571,0000091204012854572,0000091204012854573,0000091204012854574,0000091204012854575,0000091204012854576,0000091204012854577,0000091204012854578,0000091204012854579,0000091204012854580,0000091204012854581,0000091204012854582,0000091204012854583,0000091204012854584,0000091204012854585,0000091204012854586,0000091204012854587,0000091204012854588,0000091204012854589,0000091204012854590,0000091204012854591,0000091204012854592,0000091204012854593,0000091204012854594,0000091204012854595,0000091204012854596,0000091204012854597,0000091204012854598,0000091204012905303,0000091204012905304,0000091204012905305,0000091204012905313,0000091204012905315,0000091204012905317,0000091204012905319,0000091204012905322,0000091204012905323,0000091204013010717,0000091204013085712,0000091204013087727,0000091204013087732,0000091204013185544,0000091204013185552,0000091204013290297,0000091204013290865,0000091204013290866,0000091204013290867,0000091204013498383,0000091204013498384,0000091204013498385,0000091204013498386,0000091204013498387,0000091204013498388,0000091204013498389,0000091204013498390,0000091204013530057,0000091204013530058,0000091204013530059,0000091204013532882,0000091204013532883,0000091204013532884,0000091204013532885,0000091204013532886,0000091204013532887,0000091204013532888,0000091204013532889,0000091204013532890,0000091204013532891,0000091204013532892,0000091204013532893,0000091204013532894,0000091204013532895,0000091204013532896,0000091204013532897,0000091204013532898,0000091204013532899,0000091204013583372,0000091204013583373,0000091204013583374,0000091204013583375,0000091204013583376,0000091204013638006,0000091204013638007,0000091204013638430,0000091204013638431,0000091204013638432,0000091204013639136,0000091204013639137,0000091204013639138,0000091204013639139,0000091204013639140,0000091204013639141,0000091204013639142,0000091204013639143,0000091204013639144,0000091204013639145,0000091204013639146,0000091204013639147,0000091204013639148,0000091204013639149,0000091204013640589,0000091204013674492,0000091204013674493,0000091204013674494,0000091204013674524,0000091204013674692,0000091204013674694,0000091204013677003,0000091204013677004,0000091204013678469,0000091204013678470,0000091204013678471,0000091204013678472,0000091204013678473,0000091204013678474,0000091204013678475,0000091204013678476,0000091204013678477,0000091204013678478,0000091204013678479,0000091204013678480,0000091204013678481,0000091204013678482,0000091204013678483,0000091204013678484,0000091204013678485,0000091204013737633,0000091204013738188,0000091204013738189,0000091204013738190,0000091204013738191,0000091204013738192,0000091204013738193,0000091204013738194,0000091204013738195,0000091204013738196,0000091204013738197,0000091204013738198,0000091204013756478,0000091204013756479,0000091204013756480,0000091204013756481,0000091204013756482,0000091204013756483,0000091204013756484,0000091204013756485,0000091204013756486,0000091204013756487,0000091204013756488,0000091204013756489,0000091204013756490,0000091204013756491,0000091204013756492,0000091204013756493,0000091204013756494,0000091204013756495,0000091204013756496,0000091204013756497,0000091204013756498,0000091204013756499,0000091204013756500,0000091204013756501,0000091204013756502,0000091204013756503,0000091204013756504,0000091204013850532,0000091204013850561,0000091204013850562,0000091204013850563,0000091204013850807,0000091204013851642,0000091204013851825,0000091204013851826,0000091204013851827,0000091204013851828,0000091204013851829,0000091204013851830,0000091204013851831,0000091204013852335,0000091204013852729,0000091204013852730,0000091204013852731,0000091204013852732,0000091204013852733,0000091204013852734,0000091204013852735,0000091204013852736,0000091204013852737,0000091204013852738,0000091204013852739,0000091204013852740,0000091204013852741,0000091204013852814,0000091204013852815,0000091204013852816,0000091204013852820,0000091204013852821,0000091204013852822,0000091204013852823,0000091204013852824,0000091204013852825,0000091204013852826,0000091204013852827,0000091204013852828,0000091204013852829,0000091204013852830,0000091204013852831,0000091204013852832,0000091204013852833,0000091204013852834,0000091204013852835,0000091204013852836,0000091204013852837,0000091204013852838,0000091204013852839,0000091204013852840,0000091204013852841,0000091204013852842,0000091204013852843,0000091204013852844,0000091204013852845,0000091204013852846,0000091204013852847,0000091204013852848,0000091204013852849,0000091204013852850,0000091204013852851,0000091204013852852,0000091204013852853,0000091204013852916,0000091204013852917,0000091204013852918,0000091204013852919,0000091204013852920,0000091204013852921,0000091204013852922,0000091204013852923,0000091204013852944,0000091204013852945,0000091204013852946,0000091204013853937,0000091204013853938,0000091204013853939,0000091204013853940,0000091204013853941,0000091204013853942,0000091204013853943,0000091204013853944,0000091204013853945'
		set @ListPacklistID = N'13523771,13584158,14033215,14242643,14268033,14488209,14514308,14526419,14730907,14730908,14730909,14880225,14942774,14942775,14942776,14952739,14966165,14986745,14987714,14998226,14998826,15014943,15021152,15021153,15021154,15048162,15048301,15048302,15048303,15048304,15048305,15048306,15048307,15048308,15048309,15048310,15048311,15048312,15048313,15048336,15048337,15048338,15048341,15048342,15048343,15048344,15048345,15048346,15048347,15048348,15048349,15048350,15048351,15048384,15048385,15048386,15048387,15048406,15048709,15048710,15048711,15048712,15048713,15048714,15048715,15048716,15048717,15048718,15048719,15048720,15048721,15048722,15048723,15048724,15048725,15048726,15048780,15048781,15048782,15048812,15048813,15048814,15048860,15048862,15048921,15053597,15053872,15053873,15053874,15053875,15053876,15053877,15053878,15053879,15053880,15053881,15053882,15053883,15053884,15053885,15053886,15053887,15053888,15053889,15053890,15053891,15053892,15053893,15053894,15053895,15053897,15053898,15053899,15059536,15059537,15059616,15059617,15059618,15059619,15059620,15059621,15059622,15059623,15059624,15059625,15059650,15059651,15059652,15059653,15059654,15059655,15059656,15059671,15059672,15059673,15059674,15059675,15059676,15059677,15059678,15059679,15059680,15059681,15059682,15059683,15059684,15059690,15059692,15059693,15059694,15059695,15059696,15059697,15059698,15059699,15059700,15059701,15059702,15059703,15059704,15059705,15059711,15059712,15059713,15059715,15059716,15059717,15059718,15059719,15059720,15059721,15059722,15059727,15059728,15059729,15059730,15059731,15059732,15059733,15059734,15059735,15059736,15059739,15059765,15059766,15059767,15059768,15059769,15059770,15059771,15059794,15059795,15059887,15059888,15059889,15059890,15059891,15059892,15059893'
---split cartons and put in temp table to make it easier to use
 
WHILE LEN(@ListCartonId) > 0
    BEGIN
        SET @ListCartonIdIns      = LEFT(@ListCartonId, 
                                ISNULL(NULLIF(CHARINDEX(',', @ListCartonId) - 1, -1),
                                LEN(@ListCartonId)))
        SET @ListCartonId = SUBSTRING(@ListCartonId,
                                     ISNULL(NULLIF(CHARINDEX(',', @ListCartonId), 0),
                                     LEN(@ListCartonId)) + 1, LEN(@ListCartonId))

            INSERT INTO @ListCartonIdtbl( carton )
        VALUES ( @ListCartonIdIns )
END


WHILE LEN(@ListPacklistID) > 0
    BEGIN
        SET @ListPacklistIDIns      = LEFT(@ListPacklistID, 
                                ISNULL(NULLIF(CHARINDEX(',', @ListPacklistID) - 1, -1),
                                LEN(@ListPacklistID)))
        SET @ListPacklistID = SUBSTRING(@ListPacklistID,
                                     ISNULL(NULLIF(CHARINDEX(',', @ListPacklistID), 0),
                                     LEN(@ListPacklistID)) + 1, LEN(@ListPacklistID))

            INSERT INTO @ListPacklistIDtbl( pack )
        VALUES ( @ListPacklistIDIns )
END


select * from @ListCartonIdtbl cross apply @ListPacklistIDtbl

Karthik
http://karthik4identity.blogspot.com/

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-04-27 : 06:45:38

quote:
Originally posted by collie

quote:
Originally posted by visakh16

http://visakhm.blogspot.com/2010/02/parsing-delimited-string.html

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Thanks
How can i use the example with 2 different comma delimited parameters?
I tried loop within loop but it takes a very long time.

Whisky-my beloved dog who died suddenly on the 29/06/06-I miss u so much.

can you illustrate what you want? what all you pass and what you want as o/p?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

collie
Constraint Violating Yak Guru

400 Posts

Posted - 2010-04-27 : 07:10:41

Parameters ListCartonId and ListPacklistID can be much longer than the sample.

set @ListCartonId = N'0000091204001744483,0000091204001744484,0000091204001744485,0000091204001744491'
set @ListPacklistID = N'13523771,13584158,14033215,14242643'

output
carton packlist
0000091204001744483 13523771
0000091204001744484 13584158
0000091204001744485 14033215
0000091204001744491 14242643

Whisky-my beloved dog who died suddenly on the 29/06/06-I miss u so much.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-04-27 : 07:20:59

[code]SELECT f1.Val AS carton,f2.Val AS packlist
FROM dbo.ParseValues(@ListCartonId,',') f1
LEFT JOIN dbo.ParseValues(@ListPacklistID,',') f2
ON f1.ID=f2.ID
[/code]

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

karthik_padbanaban
Constraint Violating Yak Guru

263 Posts

Posted - 2010-04-27 : 07:22:30

quote:
Originally posted by collie

Parameters ListCartonId and ListPacklistID can be much longer than the sample.

set @ListCartonId = N'0000091204001744483,0000091204001744484,0000091204001744485,0000091204001744491'
set @ListPacklistID = N'13523771,13584158,14033215,14242643'

output
carton packlist
0000091204001744483 13523771
0000091204001744484 13584158
0000091204001744485 14033215
0000091204001744491 14242643

Whisky-my beloved dog who died suddenly on the 29/06/06-I miss u so much.

check this out..!!


declare @newValue  nvarchar (50)
  declare @ListCartonId  nvarchar (max) --delimited id string
declare @ListPacklistID  nvarchar (max) --delimited id string
 

declare @err nvarchar (500)
declare  @UserId int
declare @notice_id int
declare @NoPlnotice_id int  
declare @NoCartonNotice_id int  
declare @PODnotice_id int 
DECLARE @Partial nvarchar (max)
declare @count int
set @UserId=99

  declare @del nvarchar (10) --delimiter
 declare @nextstr nvarchar(4000)
DECLARE @nextstrPL  nvarchar(4000)
 declare @pos int
declare @posPL int
 declare @nextpos int
 declare @nextposPL int
declare @statusId int
declare @message nvarchar (100)
DECLARE @PODmessage nvarchar (100)
declare @NoPlMessage nvarchar (50)
declare @NoCartonMessage nvarchar (50)
declare @ListCartonIdIns nvarchar(max)
declare @ListPacklistIDIns nvarchar(max)

declare  @ListCartonIdtbl table(id int identity(1,1),carton varchar(100))
declare  @ListPacklistIDtbl table(id int identity(1,1),pack varchar(100))

set @newValue = N'pod' 
	set	@ListCartonId = N'0000091204001744483,0000091204001744484,0000091204001744485,0000091204001744491,0000091204001744492,0000091204001744493,0000091204001744494,0000091204001744495,0000091204002790095,0000091204002790096,0000091204002790097,0000091204002790098,0000091204002790101,0000091204002790102,0000091204002790103,0000091204002790104,0000091204002790105,0000091204002790107,0000091204002790108,0000091204002790109,0000091204002790111,0000091204002790112,0000091204002790113,0000091204011238629,0000091204011238630,0000091204011240000,0000091204011240001,0000091204011240002,0000091204011240003,0000091204011240004,0000091204011240005,0000091204011900421,0000091204011900422,0000091204011902744,0000091204011902745,0000091204011902746,0000091204011902747,0000091204011986914,0000091204011986915,0000091204011986916,0000091204011986917,0000091204011986918,0000091204011986919,0000091204011986920,0000091204011986974,0000091204011986975,0000091204011986976,0000091204011986977,0000091204011986978,0000091204011992033,0000091204011992034,0000091204011992035,0000091204011992036,0000091204011992037,0000091204011992038,0000091204011992039,0000091204011992040,0000091204012028056,0000091204012028057,0000091204012028058,0000091204012033833,0000091204012033834,0000091204012145857,0000091204012355411,0000091204012758098,0000091204012758099,0000091204012758100,0000091204012758101,0000091204012758102,0000091204012758103,0000091204012758104,0000091204012758105,0000091204012758106,0000091204012758107,0000091204012758108,0000091204012758109,0000091204012758110,0000091204012853019,0000091204012853020,0000091204012853021,0000091204012853022,0000091204012853023,0000091204012853024,0000091204012853025,0000091204012853272,0000091204012853273,0000091204012853274,0000091204012853708,0000091204012853709,0000091204012853768,0000091204012853769,0000091204012853782,0000091204012853783,0000091204012853784,0000091204012853785,0000091204012853786,0000091204012853787,0000091204012853788,0000091204012853789,0000091204012853790,0000091204012853795,0000091204012853796,0000091204012853797,0000091204012853798,0000091204012853799,0000091204012853800,0000091204012853801,0000091204012853802,0000091204012853803,0000091204012853804,0000091204012853805,0000091204012853806,0000091204012853807,0000091204012853808,0000091204012853809,0000091204012853839,0000091204012853842,0000091204012853843,0000091204012853844,0000091204012853845,0000091204012853846,0000091204012853847,0000091204012853848,0000091204012853849,0000091204012853850,0000091204012853851,0000091204012853852,0000091204012853853,0000091204012853854,0000091204012853855,0000091204012853856,0000091204012853857,0000091204012853858,0000091204012853859,0000091204012853860,0000091204012853861,0000091204012853862,0000091204012853863,0000091204012853864,0000091204012853865,0000091204012853895,0000091204012853896,0000091204012853897,0000091204012853898,0000091204012853910,0000091204012853911,0000091204012853912,0000091204012853913,0000091204012853914,0000091204012853915,0000091204012853916,0000091204012853917,0000091204012853918,0000091204012853919,0000091204012853920,0000091204012853922,0000091204012853923,0000091204012853924,0000091204012853925,0000091204012853926,0000091204012853938,0000091204012853939,0000091204012853940,0000091204012853941,0000091204012853942,0000091204012853943,0000091204012853944,0000091204012853945,0000091204012853946,0000091204012853947,0000091204012853948,0000091204012853949,0000091204012853950,0000091204012853951,0000091204012853952,0000091204012853953,0000091204012853954,0000091204012853955,0000091204012853956,0000091204012853957,0000091204012853958,0000091204012853959,0000091204012853960,0000091204012853961,0000091204012853962,0000091204012853971,0000091204012853972,0000091204012853973,0000091204012853974,0000091204012853975,0000091204012853976,0000091204012853977,0000091204012853978,0000091204012853979,0000091204012853980,0000091204012853981,0000091204012854095,0000091204012854097,0000091204012854098,0000091204012854099,0000091204012854100,0000091204012854101,0000091204012854102,0000091204012854103,0000091204012854115,0000091204012854525,0000091204012854526,0000091204012854527,0000091204012854528,0000091204012854529,0000091204012854530,0000091204012854531,0000091204012854532,0000091204012854533,0000091204012854534,0000091204012854535,0000091204012854536,0000091204012854537,0000091204012854538,0000091204012854539,0000091204012854540,0000091204012854541,0000091204012854542,0000091204012854543,0000091204012854544,0000091204012854545,0000091204012854546,0000091204012854547,0000091204012854548,0000091204012854549,0000091204012854550,0000091204012854551,0000091204012854552,0000091204012854553,0000091204012854554,0000091204012854555,0000091204012854556,0000091204012854557,0000091204012854558,0000091204012854559,0000091204012854560,0000091204012854561,0000091204012854562,0000091204012854563,0000091204012854564,0000091204012854565,0000091204012854566,0000091204012854567,0000091204012854568,0000091204012854569,0000091204012854570,0000091204012854571,0000091204012854572,0000091204012854573,0000091204012854574,0000091204012854575,0000091204012854576,0000091204012854577,0000091204012854578,0000091204012854579,0000091204012854580,0000091204012854581,0000091204012854582,0000091204012854583,0000091204012854584,0000091204012854585,0000091204012854586,0000091204012854587,0000091204012854588,0000091204012854589,0000091204012854590,0000091204012854591,0000091204012854592,0000091204012854593,0000091204012854594,0000091204012854595,0000091204012854596,0000091204012854597,0000091204012854598,0000091204012905303,0000091204012905304,0000091204012905305,0000091204012905313,0000091204012905315,0000091204012905317,0000091204012905319,0000091204012905322,0000091204012905323,0000091204013010717,0000091204013085712,0000091204013087727,0000091204013087732,0000091204013185544,0000091204013185552,0000091204013290297,0000091204013290865,0000091204013290866,0000091204013290867,0000091204013498383,0000091204013498384,0000091204013498385,0000091204013498386,0000091204013498387,0000091204013498388,0000091204013498389,0000091204013498390,0000091204013530057,0000091204013530058,0000091204013530059,0000091204013532882,0000091204013532883,0000091204013532884,0000091204013532885,0000091204013532886,0000091204013532887,0000091204013532888,0000091204013532889,0000091204013532890,0000091204013532891,0000091204013532892,0000091204013532893,0000091204013532894,0000091204013532895,0000091204013532896,0000091204013532897,0000091204013532898,0000091204013532899,0000091204013583372,0000091204013583373,0000091204013583374,0000091204013583375,0000091204013583376,0000091204013638006,0000091204013638007,0000091204013638430,0000091204013638431,0000091204013638432,0000091204013639136,0000091204013639137,0000091204013639138,0000091204013639139,0000091204013639140,0000091204013639141,0000091204013639142,0000091204013639143,0000091204013639144,0000091204013639145,0000091204013639146,0000091204013639147,0000091204013639148,0000091204013639149,0000091204013640589,0000091204013674492,0000091204013674493,0000091204013674494,0000091204013674524,0000091204013674692,0000091204013674694,0000091204013677003,0000091204013677004,0000091204013678469,0000091204013678470,0000091204013678471,0000091204013678472,0000091204013678473,0000091204013678474,0000091204013678475,0000091204013678476,0000091204013678477,0000091204013678478,0000091204013678479,0000091204013678480,0000091204013678481,0000091204013678482,0000091204013678483,0000091204013678484,0000091204013678485,0000091204013737633,0000091204013738188,0000091204013738189,0000091204013738190,0000091204013738191,0000091204013738192,0000091204013738193,0000091204013738194,0000091204013738195,0000091204013738196,0000091204013738197,0000091204013738198,0000091204013756478,0000091204013756479,0000091204013756480,0000091204013756481,0000091204013756482,0000091204013756483,0000091204013756484,0000091204013756485,0000091204013756486,0000091204013756487,0000091204013756488,0000091204013756489,0000091204013756490,0000091204013756491,0000091204013756492,0000091204013756493,0000091204013756494,0000091204013756495,0000091204013756496,0000091204013756497,0000091204013756498,0000091204013756499,0000091204013756500,0000091204013756501,0000091204013756502,0000091204013756503,0000091204013756504,0000091204013850532,0000091204013850561,0000091204013850562,0000091204013850563,0000091204013850807,0000091204013851642,0000091204013851825,0000091204013851826,0000091204013851827,0000091204013851828,0000091204013851829,0000091204013851830,0000091204013851831,0000091204013852335,0000091204013852729,0000091204013852730,0000091204013852731,0000091204013852732,0000091204013852733,0000091204013852734,0000091204013852735,0000091204013852736,0000091204013852737,0000091204013852738,0000091204013852739,0000091204013852740,0000091204013852741,0000091204013852814,0000091204013852815,0000091204013852816,0000091204013852820,0000091204013852821,0000091204013852822,0000091204013852823,0000091204013852824,0000091204013852825,0000091204013852826,0000091204013852827,0000091204013852828,0000091204013852829,0000091204013852830,0000091204013852831,0000091204013852832,0000091204013852833,0000091204013852834,0000091204013852835,0000091204013852836,0000091204013852837,0000091204013852838,0000091204013852839,0000091204013852840,0000091204013852841,0000091204013852842,0000091204013852843,0000091204013852844,0000091204013852845,0000091204013852846,0000091204013852847,0000091204013852848,0000091204013852849,0000091204013852850,0000091204013852851,0000091204013852852,0000091204013852853,0000091204013852916,0000091204013852917,0000091204013852918,0000091204013852919,0000091204013852920,0000091204013852921,0000091204013852922,0000091204013852923,0000091204013852944,0000091204013852945,0000091204013852946,0000091204013853937,0000091204013853938,0000091204013853939,0000091204013853940,0000091204013853941,0000091204013853942,0000091204013853943,0000091204013853944,0000091204013853945'
		set @ListPacklistID = N'13523771,13584158,14033215,14242643,14268033,14488209,14514308,14526419,14730907,14730908,14730909,14880225,14942774,14942775,14942776,14952739,14966165,14986745,14987714,14998226,14998826,15014943,15021152,15021153,15021154,15048162,15048301,15048302,15048303,15048304,15048305,15048306,15048307,15048308,15048309,15048310,15048311,15048312,15048313,15048336,15048337,15048338,15048341,15048342,15048343,15048344,15048345,15048346,15048347,15048348,15048349,15048350,15048351,15048384,15048385,15048386,15048387,15048406,15048709,15048710,15048711,15048712,15048713,15048714,15048715,15048716,15048717,15048718,15048719,15048720,15048721,15048722,15048723,15048724,15048725,15048726,15048780,15048781,15048782,15048812,15048813,15048814,15048860,15048862,15048921,15053597,15053872,15053873,15053874,15053875,15053876,15053877,15053878,15053879,15053880,15053881,15053882,15053883,15053884,15053885,15053886,15053887,15053888,15053889,15053890,15053891,15053892,15053893,15053894,15053895,15053897,15053898,15053899,15059536,15059537,15059616,15059617,15059618,15059619,15059620,15059621,15059622,15059623,15059624,15059625,15059650,15059651,15059652,15059653,15059654,15059655,15059656,15059671,15059672,15059673,15059674,15059675,15059676,15059677,15059678,15059679,15059680,15059681,15059682,15059683,15059684,15059690,15059692,15059693,15059694,15059695,15059696,15059697,15059698,15059699,15059700,15059701,15059702,15059703,15059704,15059705,15059711,15059712,15059713,15059715,15059716,15059717,15059718,15059719,15059720,15059721,15059722,15059727,15059728,15059729,15059730,15059731,15059732,15059733,15059734,15059735,15059736,15059739,15059765,15059766,15059767,15059768,15059769,15059770,15059771,15059794,15059795,15059887,15059888,15059889,15059890,15059891,15059892,15059893'
---split cartons and put in temp table to make it easier to use
 
WHILE LEN(@ListCartonId) > 0
    BEGIN
        SET @ListCartonIdIns      = LEFT(@ListCartonId, 
                                ISNULL(NULLIF(CHARINDEX(',', @ListCartonId) - 1, -1),
                                LEN(@ListCartonId)))
        SET @ListCartonId = SUBSTRING(@ListCartonId,
                                     ISNULL(NULLIF(CHARINDEX(',', @ListCartonId), 0),
                                     LEN(@ListCartonId)) + 1, LEN(@ListCartonId))

            INSERT INTO @ListCartonIdtbl( carton )
        VALUES ( @ListCartonIdIns )
END


WHILE LEN(@ListPacklistID) > 0
    BEGIN
        SET @ListPacklistIDIns      = LEFT(@ListPacklistID, 
                                ISNULL(NULLIF(CHARINDEX(',', @ListPacklistID) - 1, -1),
                                LEN(@ListPacklistID)))
        SET @ListPacklistID = SUBSTRING(@ListPacklistID,
                                     ISNULL(NULLIF(CHARINDEX(',', @ListPacklistID), 0),
                                     LEN(@ListPacklistID)) + 1, LEN(@ListPacklistID))

            INSERT INTO @ListPacklistIDtbl( pack )
        VALUES ( @ListPacklistIDIns )
END

select carton,pack from @ListCartonIdtbl a join @ListPacklistIDtbl b on a.id = b.id

Karthik
http://karthik4identity.blogspot.com/

DBA in the making
Aged Yak Warrior

638 Posts

Posted - 2010-04-27 : 07:26:51

A CLR function would be pretty fast here. The CLR string datatype has a split function that could do this for you.

There's an example of this here:

http://www.codeproject.com/KB/database/TableValuedFnsAsArrays.aspx

------------------------------------------------------------------------------------
Any and all code contained within this post comes with a 100% money back guarantee.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources