Link to home
Start Free TrialLog in
Avatar of ALM123
ALM123

asked on

SQL slow when updating large number of records

Hi,

Im a newbie to SQL but have been struggling with this for a while:

I have a large table(approx 2M records) as shown below called test. I have to strip the eighth digit and add one to the seventh digit. Then pass the new number into my stored procedure which will then create a new eighth digit. My attached script works but is far too slow for what is required. Id_no is the primary key which has a clustered index.  

Things tried.
Adding extra indexes.
Used integer instead of varchar and converted back where necessary.

Test

Id_no      number_1      number_2
Int(PK)      varchar(8)      varchar(6)

29067783      36324323      630502
29067795      00856389      311518
29067803      30505702      218968
29067811      80731375      295942
29067816      13262414      540058
29067817      52513434      534148
29067829      96974118      545003
29067831      70515700      234324
29067836      00846489      201148
29067840      20716619      106305

Reading other articles suggest using a cursor is not the best idea but it seemed easiest as Id_no is not sequential.  Currently it is taking 9 mins to update 10k records. If I remove the call to my stored procedure the update is still taking over a minute. If I remove both then the script takes 3 seconds.

Any help greatly appreciated!

BEGIN
DECLARE
	@number2 	varchar(6)
	,@number1	varchar(8)
	,@Id_no 	int
	,@new_no 	varchar(8) 
	,@new_no_2 	varchar(8)
	,@log_count 	int
	,@trans_max 	int
	,@end_of_csr 	varchar(1)
	,@count 	int
 
SET NOCOUNT ON
 
SET @log_count = 0
SET @trans_max = 200
set @end_of_csr = 'N'
set @count=0
BEGIN TRANSACTION
DECLARE csr_test cursor 
FOR
	(
		SELECT
 		Id_no ,number_2,number_1
 
	FROM
 	Test 	
	)
COMMIT TRANSACTION
OPEN csr_test
WHILE @end_of_csr = 'N'
BEGIN
	FETCH NEXT FROM csr_test
	INTO
	@Id_no,
	@number2,
	@number1 
	--if @Id_no = 27291669
	--begin
	--	SET @end_of_csr = 'Y'
	--end
	IF @@FETCH_STATUS <> 0
	BEGIN
		SET @end_of_csr = 'Y'
	END
		SET @new_no  = LEFT(@number1,7)
		SET @new_no  = CAST(@new_no  as int) + 1
		IF LEN(@new_no ) = 4
		BEGIN
			SET @new_no  = '000'+ @new_no 
		END		
		IF LEN(@new_no ) = 5
		BEGIN
			SET @new_no  = '00' + @new_no 
		END
		IF LEN(@new_no ) = 6
		BEGIN 
			SET @new_no  = '0' + @new_no  
		END
		
		SET @count=@count+1
		BEGIN TRANSACTION
		SET @log_count = @log_count + 1
		IF @log_count > @trans_max
		BEGIN --TRANSACTION
			SET @log_count = 1
		END	
			EXEC usp_mystoredproc @number2, @new_no , @new_no_2 OUTPUT	
			IF LEN(@new_no_2) = 3
			BEGIN
				SET @new_no_2 = '00000'+ @new_no_2
			END	
			IF LEN(@new_no_2) = 4
			BEGIN
				SET @new_no_2 = '0000'+ @new_no_2
			END		
			IF LEN(@new_no_2) = 5
			BEGIN
				SET @new_no_2 = '000' + @new_no_2
			END
			IF LEN(@new_no_2) = 6
			BEGIN 
				SET @new_no_2 = '00' + @new_no_2 
			END
			IF LEN(@new_no_2) = 7
			BEGIN 
				SET @new_no_2 = '0' + @new_no_2 
			END
			
			PRINT 'updating account ' + CAST(@new_no  AS VARCHAR(8)) + '   ' + CAST(@Id_no AS VARCHAR(10))+ '  from  ' + CAST(@number1 AS VARCHAR(8))+ ' '+CAST(@number2 AS VARCHAR(7))+ '   ' + cast(@COUNT as VARCHAR(9))
			--UPDATE Test SET number_1 = @new_no 
			--WHERE [Id_no] = @Id_no
			UPDATE Test 
			SET number_1 = @new_no2
			WHERE CURRENT of csr_test
			COMMIT TRANSACTION
						
	END
END
--SET statistics IO OFF
 
BEGIN
	CLOSE csr_test
	DEALLOCATE csr_test
END

Open in new window

Avatar of Lowfatspread
Lowfatspread
Flag of United Kingdom of Great Britain and Northern Ireland image
more like this...

i'm not sure you actually want the begin/commit trransctions...

yes don't use a cursor if avoidable...

can you convert the stored procedure into a function ?
 in that case you could just use a single update statement...

hth
BEGIN
DECLARE
      @number2       varchar(6)
      ,@Id_old       int
      ,@Id_no       int
      ,@new_no       varchar(8) 
      ,@new_no_2       varchar(8)
      ,@log_count       int
 
 
      ,@count       int
 
SET NOCOUNT ON
 
SElect @log_count = 0
      ,@trans_max = 200
      ,@count=0
      ,@id_no = 0
      ,@id_old=0
 
begin transaction 
 
While Exists (select id_no from test where id_no > = @id_old)
Begin
            SELECT top 1
             @id_no= Id_no
            ,@number2=number_2
            ,@new_no=Right('0000'+convert(varchar(7),convert(integer,substring(number_1,1,7))+1),7)  
            ,@count=@count+1
            ,@log_count=@log_count+1
             FROM Test       
             where id_no >=@id_old      
            
            IF @log_count > @trans_max
            BEGIN 
                  commit transaction
                  SET @log_count = 1
                  begin transaction 
            END      
 
            EXEC usp_mystoredproc @number2, @new_no , @new_no_2 OUTPUT      
 
            Set @new_no_2 = right('00000'+@new_no_2,8)        
                  
            PRINT 'updating account ' + CAST(@new_no  AS VARCHAR(8)) + '   ' + CAST(@Id_no AS VARCHAR(10))+ '  from  ' + CAST(@number1 AS VARCHAR(8))+ ' '+CAST(@number2 AS VARCHAR(7))+ '   ' + cast(@COUNT as VARCHAR(9))
 
            UPDATE Test 
               SET number_1 = @new_no2
             WHERE id_no=@id_no
 
            set @id_old=@id_no + 1
 
END
 
Commit transaction

Open in new window

Avatar of Lowfatspread
Lowfatspread
Flag of United Kingdom of Great Britain and Northern Ireland image
sorry my solution just reduced the amount of processing and number of conditional tests being perform for each row ....

Avatar of David Todd
David Todd
Flag of New Zealand image
Hi,

Just somethign I noticed about Lowfat's code, shouldn't lines 28 and 43 yes left rather than right?

Cheers
  David
Avatar of Lowfatspread
Lowfatspread
Flag of United Kingdom of Great Britain and Northern Ireland image
don't think so he wants leading zeroes ?

e.g.  right('00000' + 4567,8)    = 00004567  
Avatar of Lowfatspread
Lowfatspread
Flag of United Kingdom of Great Britain and Northern Ireland image
but

 SELECT top 1
             @id_no= Id_no
            ,@number2=number_2
            ,@new_no=Right('0000'+convert(varchar(7),convert(integer,substring(number_1,1,7))+1),7)  
            ,@count=@count+1
            ,@log_count=@log_count+1
             FROM Test      
             where id_no >=@id_old    

does require an order by to be added
e.g.

 SELECT top 1
             @id_no= Id_no
            ,@number2=number_2
            ,@new_no=Right('0000'+convert(varchar(7),convert(integer,substring(number_1,1,7))+1),7)  
            ,@count=@count+1
            ,@log_count=@log_count+1
             FROM Test      
             where id_no >=@id_old    
             order by id_no
Avatar of ALM123
ALM123

ASKER

Hi,
Thanks for looking at this, Ive tested both above and stil seems to be taking about the same time. Ive attached the stored procedure to see if anyone can think of anything else. Many Thanks again. It is just performing some basic mod check function which I thought wouldnt take any time to process. Im open to using a function or anything really. Thanks again.

CREATE PROCEDURE [dbo].[usp_moduluscheck2]
		@number2  	varchar(6)
		,@number1  	varchar(8)
		,@new_number1 	varchar(8)  OUTPUT	--varchar(8) 	OUTPUT
AS
 
 
DECLARE
	@u SMALLINT -- Value for u
	,@v SMALLINT                   
	,@w SMALLINT                   
	,@x SMALLINT                   
	,@y SMALLINT                   
	,@z SMALLINT                   
	,@a SMALLINT                   
	,@b SMALLINT                   
	,@c SMALLINT                   
	,@d SMALLINT                   
	,@e SMALLINT                   
	,@f SMALLINT                   
	,@g SMALLINT                   
	,@h SMALLINT 
	,@Wu SMALLINT -- weighting for u
	,@Wv SMALLINT
	,@Ww SMALLINT
	,@Wx SMALLINT
	,@Wy SMALLINT
	,@Wz SMALLINT
	,@Wa SMALLINT
	,@Wb SMALLINT
	,@Wc SMALLINT
	,@Wd SMALLINT
	,@We SMALLINT
	,@Wf SMALLINT
	,@Wg SMALLINT
	,@Wh SMALLINT
	,@ValidationId_no INT -- Weighting row currently being applied
	,@Ctr INT -- loop counter
	,@MaxCtr INT -- loop max value
	,@ModCheck VARCHAR(5) -- 1 of 3 modulus check types
	,@ModINT -- result of modulus(weightsum)
	,@WeightSum INT -- Result of summing(weights)
	,@Exception VARCHAR(3) -- exception rule for this weighting
	,@number2a 	varchar(6)
	,@number1a 	varchar(8)
	,@new_number1a   varchar(8)
 
 
--set @number2  = '515003'
--set @number1  = '9677412'
 
BEGIN
 
SET NOCOUNT ON
SET CONCAT_NULL_YIELDS_NULL OFF
 
IF OBJECT_ID('dbo.WR') IS NOT NULL
BEGIN
	DROP TABLE [dbo].[WR]
END
CREATE TABLE [dbo].[WR]
	(
	[Id] INT IDENTITY(1,1) NOT NULL PRIMARY KEY
	,Id_no INT NOT NULL
	)
 
INSERT INTO dbo.WR
	(
		Id_no
	)
	SELECT
		v.Id_no
	FROM
		dbo.bank_validation v
	INNER JOIN	(
					SELECT
						 starting_sortcode
						,ending_sortcode
					FROM
						dbo.bank_validation
					WHERE
						@number2  BETWEEN starting_sortcode AND ending_sortcode
					GROUP BY
						 starting_sortcode
						,ending_sortcode
				) c
		ON
				v.starting_sortcode = c.starting_sortcode
			AND
				v.ending_sortcode = c.ending_sortcode
	ORDER BY
		v.Id_no
 
	
--print @ValidationId_no	
 
SELECT	
		 @Ctr = MIN([Id]) 
		,@MaxCtr = MAX([Id])
	FROM
		dbo.WR
	
	SELECT
			@ValidationId_no = Id_no
		FROM
			dbo.WR
		WHERE
			[Id] = @Ctr
	-- Where two rows exist they must be performed in the order that they appear.
	WHILE @Ctr <= @MaxCtr
		BEGIN	
		SELECT
			 @Wu = weighting_string_1
			,@Wv = weighting_string_2
			,@Ww = weighting_string_3
			,@Wx = weighting_string_4
			,@Wy = weighting_string_5
			,@Wz = weighting_string_6
			,@Wa = weighting_string_7
			,@Wb = weighting_string_8
			,@Wc = weighting_string_9
			,@Wd = weighting_string_10
			,@We = weighting_string_11
			,@Wf = weighting_string_12
			,@Wg = weighting_string_13
			,@Wh = weighting_string_14
			,@ModCheck = algorithm
			,@Exception = exception_code
		FROM
			dbo.bank_validation v
 
		WHERE
			v.Id_no = @ValidationId_no
			--PRINT @ModCheck 
			--SET @number2 a=CONVERT(varchar,@number2 )
			--SET @number1 a=CONVERT(varchar,@number1 )
			SET @u = LEFT(@number2 ,1)
			SET @v = SUBSTRING(@number2 ,2,1)
			SET @w = SUBSTRING(@number2 ,3,1)
			SET @x = SUBSTRING(@number2 ,4,1)
			SET @y = SUBSTRING(@number2 ,5,1)
			SET @z = SUBSTRING(@number2 ,6,1)
			SET @a = LEFT(@number1 ,1)
			SET @b = SUBSTRING(@number1 ,2,1)
			SET @c = SUBSTRING(@number1 ,3,1)
			SET @d = SUBSTRING(@number1 ,4,1)
			SET @e = SUBSTRING(@number1 ,5,1)
			SET @f = SUBSTRING(@number1 ,6,1)
			SET @g = SUBSTRING(@number1 ,7,1)
			SET @h = SUBSTRING(@number1 ,8,1)
			
		
 
DECLARE 	@calc1  int
		,@calc2  int
		
IF @ModCheck = 'MOD10'
	 	BEGIN -- MOD10 Check
			SET @WeightSum =
					(@u*@Wu)+(@v*@Wv)+(@w*@Ww)+(@x*@Wx)+(@y*@Wy)+(@z * @Wz) 
					(@a*@Wa)+(@b*@Wb)+(@c*@Wc)+(@d*@Wd)+(@e*@We)+(@f*@Wf)+(@g*@Wg) 
			SET @Mod= @WeightSum % 10
			SET @calc1 =  @WeightSum + 10 -@modulus
			SET @calc2 = @calc1 - @WeightSum 
		
		END	 
 
IF @ModCheck = 'MOD11'
		BEGIN
			SET @WeightSum = (@u*@Wu)+(@v*@Wv)+(@w*@Ww)+(@x*@Wx)+(@y*@Wy)+(@z*@Wz) +
					(@a*@Wa)+(@b*@Wb)+(@c*@Wc)+(@d*@Wd)+(@e*@We)+(@f*@Wf)+(@g*@Wg)+(@h*@Wh)
			SET @Mod= @WeightSum % 11	
			SET @calc1 =  @WeightSum + 11 -@modulus
			SET @calc2 = @calc1 - @WeightSum
		END	
 
IF @ModCheck = 'DBLAL'
		BEGIN -- DBLAL Check		
		SET @WeightSum =
				CASE
					WHEN (@u*@Wu) <= 9 THEN (@u*@Wu)
					ELSE 	CAST(LEFT(CAST((@u*@Wu) AS VARCHAR(12)),1) AS INT) + 
							CAST(RIGHT(CAST((@u*@Wu) AS VARCHAR(12)),1) AS INT)
		
				END +
				CASE
					WHEN (@v*@Wv) <= 9 THEN (@v*@Wv)
					ELSE 	CAST(LEFT(CAST((@v*@Wv) AS VARCHAR(12)),1) AS INT) + 
							CAST(RIGHT(CAST((@v*@Wv) AS VARCHAR(12)),1) AS INT)
					
				END +
				CASE
					WHEN (@w*@Ww) <= 9 THEN (@w*@Ww)
					ELSE 	CAST(LEFT(CAST((@w*@Ww) AS VARCHAR(12)),1) AS INT) + 
							CAST(RIGHT(CAST((@w*@Ww) AS VARCHAR(12)),1) AS INT)
				
				END +
				CASE
					WHEN (@x*@Wx) <= 9 THEN (@x*@Wx)
					ELSE 	CAST(LEFT(CAST((@x*@Wx) AS VARCHAR(12)),1) AS INT) + 
							CAST(RIGHT(CAST((@x*@Wx) AS VARCHAR(12)),1) AS INT)
					
				END +
				CASE
					WHEN (@y*@Wy) <= 9 THEN (@y*@Wy)
					ELSE 	CAST(LEFT(CAST((@y*@Wy) AS VARCHAR(12)),1) AS INT) + 
							CAST(RIGHT(CAST((@y*@Wy) AS VARCHAR(12)),1) AS INT)
					
				END +
				CASE
					WHEN (@z*@Wz) <= 9 THEN (@z*@Wz)
					ELSE 	CAST(LEFT(CAST((@z*@Wz) AS VARCHAR(12)),1) AS INT) + 
							CAST(RIGHT(CAST((@z*@Wz) AS VARCHAR(12)),1) AS INT)
					
				END +
				CASE
					WHEN (@a*@Wa) <= 9 THEN (@a*@Wa)
					ELSE 	CAST(LEFT(CAST((@a*@Wa) AS VARCHAR(12)),1) AS INT) + 
							CAST(RIGHT(CAST((@a*@Wa) AS VARCHAR(12)),1) AS INT)
					
				END +
				CASE
					WHEN (@b*@Wb) <= 9 THEN (@b*@Wb)
					ELSE 	CAST(LEFT(CAST((@b*@Wb) AS VARCHAR(12)),1) AS INT) + 
							CAST(RIGHT(CAST((@b*@Wb) AS VARCHAR(12)),1) AS INT)
					
				END + 
				CASE
					WHEN (@c*@Wc) <= 9 THEN (@c*@Wc)
					ELSE 	CAST(LEFT(CAST((@c*@Wc) AS VARCHAR(12)),1) AS INT) + 
							CAST(RIGHT(CAST((@c*@Wc) AS VARCHAR(12)),1) AS INT)
					
				END +
				CASE
					WHEN (@d*@Wd) <= 9 THEN (@d*@Wd)
					ELSE 	CAST(LEFT(CAST((@d*@Wd) AS VARCHAR(12)),1) AS INT) + 
							CAST(RIGHT(CAST((@d*@Wd) AS VARCHAR(12)),1) AS INT)
					
				END +
				CASE
					WHEN (@e*@We) <= 9 THEN (@e*@We)
					ELSE 	CAST(LEFT(CAST((@e*@We) AS VARCHAR(12)),1) AS INT) + 
							CAST(RIGHT(CAST((@e*@We) AS VARCHAR(12)),1) AS INT)
					
				END +
				CASE
					WHEN (@f*@Wf) <= 9 THEN (@f*@Wf)
					ELSE 	CAST(LEFT(CAST((@f*@Wf) AS VARCHAR(12)),1) AS INT) + 
							CAST(RIGHT(CAST((@f*@Wf) AS VARCHAR(12)),1) AS INT)
					
				END +
				CASE
					WHEN (@g*@Wg) <= 9 THEN (@g*@Wg)
					ELSE 	CAST(LEFT(CAST((@g*@Wg) AS VARCHAR(12)),1) AS INT) + 
							CAST(RIGHT(CAST((@g*@Wg) AS VARCHAR(12)),1) AS INT)
					
				END
			SET @Mod= @WeightSum % 10
			SET @calc1 =  @WeightSum + 10 - @modulus
			SET @calc2 = @calc1 - @WeightSum
			
		END	
	SET @new_number1= @number1  + CAST(@calc2 AS VARCHAR(1))
	SET @Ctr = @Ctr + 1
	END
END
 
TRUNCATE TABLE [dbo].[WR]
 
GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

Open in new window

Avatar of Lowfatspread
Lowfatspread
Flag of United Kingdom of Great Britain and Northern Ireland image
where is the @modulus actually set?
Avatar of ALM123
ALM123

ASKER

Apologies right script this time
		@NumberB	varchar(6)
		,@NumberA	varchar(8)
		,@New_numberA 	varchar(8)  OUTPUT	--varchar(8) 	OUTPUT
AS
 
 
DECLARE
	@u SMALLINT -- Value for u
	,@v SMALLINT                   
	,@w SMALLINT                   
	,@x SMALLINT                   
	,@y SMALLINT                   
	,@z SMALLINT                   
	,@a SMALLINT                   
	,@b SMALLINT                   
	,@c SMALLINT                   
	,@d SMALLINT                   
	,@e SMALLINT                   
	,@f SMALLINT                   
	,@g SMALLINT                   
	,@h SMALLINT 
	,@Wu SMALLINT -- weighting for u
	,@Wv SMALLINT
	,@Ww SMALLINT
	,@Wx SMALLINT
	,@Wy SMALLINT
	,@Wz SMALLINT
	,@Wa SMALLINT
	,@Wb SMALLINT
	,@Wc SMALLINT
	,@Wd SMALLINT
	,@We SMALLINT
	,@Wf SMALLINT
	,@Wg SMALLINT
	,@Wh SMALLINT
	,@ValidationId_no INT -- Weighting row currently being applied
	,@Ctr INT -- loop counter
	,@MaxCtr INT -- loop max value
	,@ModCheck VARCHAR(5) -- 1 of 3 modulus check types
	,@Mod INT -- result of modulus(weightsum)
	,@WeightSum INT -- Result of summing(weights)
 
 
SET NOCOUNT ON
SET CONCAT_NULL_YIELDS_NULL OFF
 
--set @NumberB= '515003'
--set @NumberA= '9677412'
--set @New_numberA = ''
 
BEGIN
 
SET NOCOUNT ON
SET CONCAT_NULL_YIELDS_NULL OFF
 
IF OBJECT_ID('dbo.WR') IS NOT NULL
BEGIN
	DROP TABLE [dbo].[WR]
END
CREATE TABLE [dbo].[WR]
	(
	[Id] INT IDENTITY(1,1) NOT NULL PRIMARY KEY
	,objid INT NOT NULL
	)
 
INSERT INTO dbo.WR
	(
		ObjId
	)
	SELECT
		v.ObjId
	FROM
		dbo.BV v
	INNER JOIN	(
					SELECT
						 starting_sortcode
						,ending_sortcode
					FROM
						dbo.BV
					WHERE
						@NumberB BETWEEN starting_sortcode AND ending_sortcode
					GROUP BY
						 starting_sortcode
						,ending_sortcode
				) c
		ON
				v.starting_sortcode = c.starting_sortcode
			AND
				v.ending_sortcode = c.ending_sortcode
	ORDER BY
		v.objid
 
	
--print @ValidationId_no	
 
SELECT	
		 @Ctr = MIN([Id]) 
		,@MaxCtr = MAX([Id])
	FROM
		dbo.WR
	
	SELECT
			@ValidationId_no = ObjId
		FROM
			dbo.WR
		WHERE
			[Id] = @Ctr
	-- Where two rows exist they must be performed in the order that they appear.
	WHILE @Ctr <= @MaxCtr
		BEGIN	
		SELECT
			 @Wu = weighting_string_1
			,@Wv = weighting_string_2
			,@Ww = weighting_string_3
			,@Wx = weighting_string_4
			,@Wy = weighting_string_5
			,@Wz = weighting_string_6
			,@Wa = weighting_string_7
			,@Wb = weighting_string_8
			,@Wc = weighting_string_9
			,@Wd = weighting_string_10
			,@We = weighting_string_11
			,@Wf = weighting_string_12
			,@Wg = weighting_string_13
			,@Wh = weighting_string_14
			,@ModCheck = algorithm
		FROM
			dbo.BV v
 
		WHERE
			v.objid = @ValidationId_no
			--PRINT @ModCheck 
			--SET @NumberBa=CONVERT(varchar,@NumberB)
			--SET @NumberAa=CONVERT(varchar,@NumberA)
			SET @u = LEFT(@NumberB,1)
			SET @v = SUBSTRING(@NumberB,2,1)
			SET @w = SUBSTRING(@NumberB,3,1)
			SET @x = SUBSTRING(@NumberB,4,1)
			SET @y = SUBSTRING(@NumberB,5,1)
			SET @z = SUBSTRING(@NumberB,6,1)
			SET @a = LEFT(@NumberA,1)
			SET @b = SUBSTRING(@NumberA,2,1)
			SET @c = SUBSTRING(@NumberA,3,1)
			SET @d = SUBSTRING(@NumberA,4,1)
			SET @e = SUBSTRING(@NumberA,5,1)
			SET @f = SUBSTRING(@NumberA,6,1)
			SET @g = SUBSTRING(@NumberA,7,1)
			SET @h = SUBSTRING(@NumberA,8,1)
			
		
 
DECLARE 	@calc1  int
		,@calc2  int
		,@calc3  int
IF @ModCheck = 'MOD10'
	 	BEGIN -- MOD10 Check
			-- Multiply by the weight and add the results together
			SET @WeightSum =
					(@u*@Wu)+(@v*@Wv)+(@w*@Ww)+(@x*@Wx)+(@y*@Wy)+(@z * @Wz) + -- Sortcode
					(@a*@Wa)+(@b*@Wb)+(@c*@Wc)+(@d*@Wd)+(@e*@We)+(@f*@Wf)+(@g*@Wg) -- account number
	
			-- Divide the total by the modulus (10)
			SET @Mod = @WeightSum % 10
			SET @calc1 =  @WeightSum + 10 -@Mod
			SET @calc2 = @calc1 - @WeightSum 
		
		END	 
		
 
 
IF @ModCheck = 'MOD11'
		BEGIN
			SET @WeightSum = (@u*@Wu)+(@v*@Wv)+(@w*@Ww)+(@x*@Wx)+(@y*@Wy)+(@z*@Wz) +
					(@a*@Wa)+(@b*@Wb)+(@c*@Wc)+(@d*@Wd)+(@e*@We)+(@f*@Wf)+(@g*@Wg)+(@h*@Wh)
	
			-- Divide the total by the modulus (11)
			SET @Mod = @WeightSum % 11	
			SET @calc1 =  @WeightSum + 11 -@Mod
			SET @calc2 = @calc1 - @WeightSum
			
		END	
 
IF @ModCheck = 'DBLAL'
		BEGIN -- DBLAL Check		
		SET @WeightSum =
				CASE
					WHEN (@u*@Wu) <= 9 THEN (@u*@Wu)
					ELSE 	CAST(LEFT(CAST((@u*@Wu) AS VARCHAR(12)),1) AS INT) + 
							CAST(RIGHT(CAST((@u*@Wu) AS VARCHAR(12)),1) AS INT)
					
				END +
				CASE
					WHEN (@v*@Wv) <= 9 THEN (@v*@Wv)
					ELSE 	CAST(LEFT(CAST((@v*@Wv) AS VARCHAR(12)),1) AS INT) + 
							CAST(RIGHT(CAST((@v*@Wv) AS VARCHAR(12)),1) AS INT)
					
				END +
				CASE
					WHEN (@w*@Ww) <= 9 THEN (@w*@Ww)
					ELSE 	CAST(LEFT(CAST((@w*@Ww) AS VARCHAR(12)),1) AS INT) + 
							CAST(RIGHT(CAST((@w*@Ww) AS VARCHAR(12)),1) AS INT)
				
				END +
				CASE
					WHEN (@x*@Wx) <= 9 THEN (@x*@Wx)
					ELSE 	CAST(LEFT(CAST((@x*@Wx) AS VARCHAR(12)),1) AS INT) + 
							CAST(RIGHT(CAST((@x*@Wx) AS VARCHAR(12)),1) AS INT)
					
				END +
				CASE
					WHEN (@y*@Wy) <= 9 THEN (@y*@Wy)
					ELSE 	CAST(LEFT(CAST((@y*@Wy) AS VARCHAR(12)),1) AS INT) + 
							CAST(RIGHT(CAST((@y*@Wy) AS VARCHAR(12)),1) AS INT)
					
				END +
				CASE
					WHEN (@z*@Wz) <= 9 THEN (@z*@Wz)
					ELSE 	CAST(LEFT(CAST((@z*@Wz) AS VARCHAR(12)),1) AS INT) + 
							CAST(RIGHT(CAST((@z*@Wz) AS VARCHAR(12)),1) AS INT)
					
				END +
				CASE
					WHEN (@a*@Wa) <= 9 THEN (@a*@Wa)
					ELSE 	CAST(LEFT(CAST((@a*@Wa) AS VARCHAR(12)),1) AS INT) + 
							CAST(RIGHT(CAST((@a*@Wa) AS VARCHAR(12)),1) AS INT)
					
				END +
				CASE
					WHEN (@b*@Wb) <= 9 THEN (@b*@Wb)
					ELSE 	CAST(LEFT(CAST((@b*@Wb) AS VARCHAR(12)),1) AS INT) + 
							CAST(RIGHT(CAST((@b*@Wb) AS VARCHAR(12)),1) AS INT)
					
				END + 
				CASE
					WHEN (@c*@Wc) <= 9 THEN (@c*@Wc)
					ELSE 	CAST(LEFT(CAST((@c*@Wc) AS VARCHAR(12)),1) AS INT) + 
							CAST(RIGHT(CAST((@c*@Wc) AS VARCHAR(12)),1) AS INT)
					
				END +
				CASE
					WHEN (@d*@Wd) <= 9 THEN (@d*@Wd)
					ELSE 	CAST(LEFT(CAST((@d*@Wd) AS VARCHAR(12)),1) AS INT) + 
							CAST(RIGHT(CAST((@d*@Wd) AS VARCHAR(12)),1) AS INT)
					
				END +
				CASE
					WHEN (@e*@We) <= 9 THEN (@e*@We)
					ELSE 	CAST(LEFT(CAST((@e*@We) AS VARCHAR(12)),1) AS INT) + 
							CAST(RIGHT(CAST((@e*@We) AS VARCHAR(12)),1) AS INT)
					
				END +
				CASE
					WHEN (@f*@Wf) <= 9 THEN (@f*@Wf)
					ELSE 	CAST(LEFT(CAST((@f*@Wf) AS VARCHAR(12)),1) AS INT) + 
							CAST(RIGHT(CAST((@f*@Wf) AS VARCHAR(12)),1) AS INT)
					
				END +
				CASE
					WHEN (@g*@Wg) <= 9 THEN (@g*@Wg)
					ELSE 	CAST(LEFT(CAST((@g*@Wg) AS VARCHAR(12)),1) AS INT) + 
							CAST(RIGHT(CAST((@g*@Wg) AS VARCHAR(12)),1) AS INT)
					
				END
	
			-- Divide the total by the modulus (10)
			SET @Mod = @WeightSum % 10
			SET @calc1 =  @WeightSum + 10 - @Mod
			SET @calc2 = @calc1 - @WeightSum
			
		END	
	IF @ModCheck = 'MOD11' AND @calc2=11
	BEGIN
		SET @calc2 = 0
	END
	IF @ModCheck = 'MOD11' AND @calc2=10
	BEGIN
		SET @calc2 = 0
	END
	IF @ModCheck = 'MOD10' AND @calc2=10
	BEGIN
		SET @calc2 = 0
	END
	IF @ModCheck = 'DBLAL' AND @calc2=10
	BEGIN
		SET @calc2 = 0
	END
	SET @New_numberA= @NumberA+ CAST(@calc2 AS VARCHAR(1))	
	SET @Ctr = @Ctr + 1
	END
END
 
TRUNCATE TABLE [dbo].[WR]

Open in new window

ASKER CERTIFIED SOLUTION
Avatar of Lowfatspread
Lowfatspread
Flag of United Kingdom of Great Britain and Northern Ireland image
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of Ken Selvia
Ken Selvia
Flag of United States of America image
Lowfatspread, do you think this can be done in a single update statement without loops or functions?
Avatar of ALM123
ALM123

ASKER

Thats seems better. Thanks again.