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

ALM123Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

LowfatspreadCommented:
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

0
LowfatspreadCommented:
sorry my solution just reduced the amount of processing and number of conditional tests being perform for each row ....

0
David ToddSenior DBACommented:
Hi,

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

Cheers
  David
0
The Ultimate Tool Kit for Technolgy Solution Provi

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy for valuable how-to assets including sample agreements, checklists, flowcharts, and more!

LowfatspreadCommented:
don't think so he wants leading zeroes ?

e.g.  right('00000' + 4567,8)    = 00004567  
0
LowfatspreadCommented:
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
0
ALM123Author Commented:
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

0
LowfatspreadCommented:
where is the @modulus actually set?
0
ALM123Author Commented:
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

0
LowfatspreadCommented:
i've rewritten the procedure to reduce the duplication and numbers of conditional tests being performed ..

however it would still be best to convert this into a function
CREATE PROCEDURE [dbo].[usp_moduluscheck2]
		@number2  	varchar(6)
		,@number1  	varchar(8)
		,@new_number1 	varchar(8)  OUTPUT	--varchar(8) 	OUTPUT
AS
 
DECLARE
	@ModCheck CHAR(5) -- 1 of 3 modulus check types
	,@WeightSum INT -- Result of summing(weights)
--	,@Exception VARCHAR(3) -- exception rule for this weighting
 
BEGIN
 
SET NOCOUNT ON
 
 
-- the modulus calculations are based around a weigthing for each digit of the 
-- sortcode (number2) and account numbers (number1)
 
-- MOD10 and MOD11 just multiply the digits by the weights and sum the results
-- DBLAL reduces the digits of the sum to a single digit by addition when the 
--       initial sum is greater than 9
 
-- the individual digits from the 2 strings are extracted via substring
-- and used as a temporary table sortcode rows 1 - 6 account number rows 7 - 14
 
-- the weighting values are obtained from the validation table and also assigned 
-- to rows in a temp table 
 
-- these two tables are joined on the "weight number" 
-- and the WV (weight value) calculated.    
 
-- the weightsum is then calculated 
--  and the components adjusted (DBLAL) as required...
 
 
Select @modcheck=modcheck
      ,@weightsum=Sum(case modcheck 
            when 'DBLAL' then case when ws > 9 
                                   then (ws % 10) + (ws / 10)
                                   else ws
                                   end
            else ws
            end     
          ) 
  from (
Select Wv.n 
      ,Wv.W * V.W  as WS
      ,modcheck
      ,exception
  From (
	SELECT n.n,convert(smallint,case n.n 
                   when 1 then weighting_string_1
	           when 2 then weighting_string_2
	      when 3 then weighting_string_3
	      when 4 then weighting_string_4
              when 5 then weighting_string_5
	      when 6 then weighting_string_6
	      when 7 then weighting_string_7
	      when 8 then weighting_string_8
	      when 9 then weighting_string_9
	      when 10 then weighting_string_10
	      when 11 then weighting_string_11
	      when 12 then weighting_string_12
	      when 13 then weighting_string_13
	      when 14 then weighting_string_14
                   end) as W  
	      ,ModCheck = algorithm
              ,Exception = exception_code
	 FROM (select * 
                 from dbo.bank_validation as v
                Where @number2  BETWEEN starting_sortcode AND ending_sortcode
                  and v.id_no = (select max(id_no)
                                   from dbo.bank_validation as x
                                  Where @number2 
                                  BETWEEN starting_sortcode 
                                    AND ending_sortcode)
               ) as v
         cross join (select 1 as n union select 2 union select 3
             union select 4 union select 5 union select 6 union select 7 
             union select 8 union select 9 union select 10 union select 11
             union select 12 union select 13 union select 14) as N 
      ) as Wv
  Inner Join 
 (
Select np.p,coalesce(convert(smallint,substring(N,np.p,1),0) as W
  From (select @number2 as N) as A
 cross join (select 1 as p union select 2 union select 3 union select 4
             union select 5 union select 6 ) as np
Union
Select np.p+6,coalesce(convert(smallint,substring(N,np.p,1),0) as W
  From (select @number1 as N) as A
 cross join (select 1 as p union select 2 union select 3 union select 4
             union select 5 union select 6 union select 7 union select 8) as np
) as V
 on wv.N = V.P			
) as X
group by modcheck
 
-- finally the new number is calculated with respect to the required modulus
-- calculation
 
-- only MOD10, MOD11, DBLAL modcheck values allowed for...
-- also no execption processing is required. 
 
Select @new_number1 = @number1 
                    + Cast( 
                            case @modcheck when 'MOD11' then 11 else 10 end  
                           - (@weightsum % case @modcheck when 'MOD11' then 11 else 10 end)
                           as char(1)
                          ) 
 
 
END
 
Return 
GO

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Ken SelviaRetiredCommented:
Lowfatspread, do you think this can be done in a single update statement without loops or functions?
0
ALM123Author Commented:
Thats seems better. Thanks again.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.