Packaging algorithm

See more:

SQL-Server-2005

SQL

SQL-Server

Helloo !

i have this problem creating a packaging algorithm, i wrote the one attached but its performance is VERY slow and in adequate. can anyone help me out ?
packaging:
-current levels = 3
-small box level contains boxes with 10 cards each
-medium box level contains boxes with 50 cards each i.e. 5 small boxes
-large boxes level contains 2 medium boxes with ==> the large box has 100 cards. and so on ...

this is applied to a certain number of cards so the whole operation has to loop till no cards are available anymore.

ALTER PROCEDURE [dbo].[test123]
      -- Add the parameters for the stored procedure here
     
      
AS
      Declare @CardsCount int; -- Number of cards processed
      Declare @Iterator int; -- 
      Declare @SmallLoopSize int; -- Number of batches
	  Declare @MediumLoopSize int; -- Number of batches
	  Declare @LargeLoopSize int; -- Number of batches
      Declare @Quantity int; -- number of records
      Declare @FromICCIDl varchar(50); -- Value to be inserted in the batch table
      Declare @ToICCID varchar(50);-- Value to be inserted in the batch table
      Declare @ToQuantity int;
	  Declare @ToQuantityMedium int;
	  Declare @ToQuantityLarge int;
      Declare @Name varchar(50);
   	  Declare @LargeIterator varchar(50);
      Declare @MediumIterator varchar(50) ;
      Declare @SmallIterator varchar(50) ;
	  Declare @MediumBoxFrom int ;	
      Declare @FromICCIDMedium varchar(50); -- Value to be inserted in the batch table
      Declare @ToICCIDMedium varchar(50);
	  Declare @FromICCIDLarge varchar(50);
	  Declare @ToICCIDLarge	varchar(50);  
	  Declare @LargeBoxFrom varchar(50)

  	  Set @MediumBoxFrom = 0;
	  Set @LargeBoxFrom = 0;
      Set @LargeIterator = 0;
	  Set @MediumIterator = 0;
      Set @SmallIterator = 0;
      Set @CardsCount = 1;
      Set @Quantity =   (Select count(Serial) FROM dbo.IDs); -- total number of cards
      Set @LargeLoopSize = ceiling(@Quantity/100);
      Set @MediumLoopSize = ceiling(@LargeLoopSize*2 ); 
	  Set @SmallLoopSize = ceiling(@MediumLoopSize  * 5);

	WHILE @LargeIterator  < @LargeLoopSize Begin
		IF((@Quantity -@CardsCount+1) <  1) Begin
			Set @ToQuantityLarge = @Quantity;
		 END
		 Else Begin
			 Set @ToQuantityLarge = @CardsCount+99  ;
		 End
						
		 Set @FromICCIDLarge =  (SELECT Serial = MAX(Serial) FROM  
                                  (SELECT TOP (100*(@LargeBoxFrom) + 1  ) Serial 
                                   FROM dbo.IDs 
                                   ORDER BY Serial) sub);

	     Set @ToICCIDLarge =    (SELECT Serial = MAX(Serial) FROM  
                                  (SELECT TOP (@ToQuantityLarge) Serial 
                                   FROM dbo.IDs 
                                   ORDER BY Serial) sub);

		 Set @Name =  @LargeIterator;

		 Insert Into dbo.BoxingAndStats (BoxIndex,[From], [To], BoxType)
		   Values(@Name, @FromICCIDLarge, @ToICCIDLarge, '100');




			WHILE @MediumIterator  < @MediumLoopSize Begin
				IF((@Quantity -@CardsCount+1) <  2) Begin
								  Set @ToQuantityMedium = @Quantity;
					 END
					 Else Begin
								   Set @ToQuantityMedium = @CardsCount+49  ;
					 End
						
					 Set @FromICCIDMedium =  (SELECT Serial = MAX(Serial) FROM  
                                              (SELECT TOP (50*(@MediumBoxFrom) + 1  ) Serial 
                                                 FROM dbo.IDs 
                                                 ORDER BY Serial) sub);

					 Set @ToICCIDMedium =    (SELECT Serial = MAX(Serial) FROM  
                                              (SELECT TOP (@ToQuantityMedium) Serial 
                                                 FROM dbo.IDs 
                                                 ORDER BY Serial) sub);

						 Set @Name =  @LargeIterator+'-' +@MediumIterator;

						 Insert Into dbo.BoxingAndStats (BoxIndex,[From], [To], BoxType)
						  Values(@Name, @FromICCIDMedium, @ToICCIDMedium, '50');

						 
			
    		 

				WHILE @SmallIterator  < @SmallLoopSize Begin
				  
					IF((@Quantity -@CardsCount+1) <  10) Begin
								  Set @ToQuantity = @Quantity+1;
					 END
					 Else Begin
								   Set @ToQuantity = @CardsCount + 9 ;
					 End
						
					 Set @FromICCIDl =       (SELECT Serial = MAX(Serial) FROM  
                                              (SELECT TOP (@CardsCount) Serial 
                                                 FROM dbo.IDs 
                                                 ORDER BY Serial) sub);

					 Set @ToICCID =          (SELECT Serial = MAX(Serial) FROM  
                                              (SELECT TOP (@ToQuantity) Serial 
                                                 FROM dbo.IDs 
                                                 ORDER BY Serial) sub);

							Set @Name = @LargeIterator +'-' +@MediumIterator + '-' + @SmallIterator;

							 Insert Into dbo.BoxingAndStats (BoxIndex,[From], [To], BoxType)
							 Values(@Name, @FromICCIDl, @ToICCID, '10');


							SET  @CardsCount = @CardsCount + '10';
							SET	@SmallIterator = @SmallIterator + 1
					if(@SmallIterator % 5 = 0) begin
					Set @SmallIterator =0;
					end    	
					if(@SmallIterator % 5 = 0)break
					END
			SET	@MediumIterator = @MediumIterator + 1;
			SET @MediumBoxFrom = @MediumBoxFrom +1;
			if(@MediumIterator % 2 = 0)begin	
			Set @MediumIterator=0
			end
			if(@MediumIterator % 2 = 0)	break
			END		
			SET @LargeIterator = @LargeIterator + 1;
			SET @LargeBoxFrom = @LargeBoxFrom +1
			
end