Mikelemuel - 1 year ago 106
SQL Question

# SQL Server stored procedure: finding the values of Odd and even

I'm using

`@subno`
as Input. And I had to find the odd and even numbers. 16 is not a fix and it can be any other number. My question is how to find the odd and even number of my input?

Lastly, subno includes ( . ) dot at any position. e.g 123456.789123 I need to find the odd and even number of "123456" and the odd and even number of "789123" the dot ( . ) is the separator.

Once you find the odd for the left side, sum them up together. Once you find the even number for left side sum it up as well and then add the total odd to the total even values. That goes the same for the right side eg "789123".

Please help me. this is my 2nd week of trying to find the solution. Once you find all the total values for each side, multiply them together. example "123456" - total value of odd and even * the "789123" total value of odd and even.

It is for the the check digit validation. Validating the subscriber number. after validating through the calculation it should match the calculated reference number to the valid check digit number. It's the business rule. Kind of algorithm

``````create procedure ProcedureName
(@subno VARCHAR(16),   --Input the 16 subscriber number
@result INT OUT,
)
as
begin
IF(LEN(@subno) <> 16)
SET @result = 1 -- INVALID RESULT
ELSE
IF(@subno % 2 = 0)
SET @result = @subno - even numbers
ELSE
SET @result = @subno --odd numbers
end
``````

Please see below my sample work

``````-- this is the sample

create procedure   ProcedureName
(
@subno VARCHAR(20),   --Subscriber no
@result INT OUT,      --result is invalid for 1, valid for 0
@payamt int
)
as

DECLARE @WA VARCHAR(2)
DECLARE @Weights varchar(9)
DECLARE @I INT
DECLARE @WD INT
DECLARE @WP INT
DECLARE @A INT
DECLARE @B INT
DECLARE @R INT
DECLARE @WR INT

SET @WR = 0
SET @R = 0
SET @A = 0
SET @B = 0
SET @WP = 0
SET @I = 0

BEGIN

IF (LEN(@subNo) = 7) AND (SUBSTRING(@subno,1,1) = '2') OR (SUBSTRING(@subno,1,1) = '9')
BEGIN
SET @result = 0 --VALID
END
ELSE IF(LEN(@subno) = 8) AND (SUBSTRING(@subno,1,1) = '2') OR
(SUBSTRING(@subno,1,1) = '9')
BEGIN
SET @result = 0 --VALID
END
ELSE IF(LEN(@subno) = 9)
BEGIN
SET @WA = SUBSTRING(@subno,1,2)
IF(@WA = '65')
set @result = 1 -- INVALID
else
BEGIN
SET @Weights = '12121212'
SET @WA = SUBSTRING(@subno,9,1)
SET @WD = 0
SET @I = 1
WHILE @I<9
BEGIN
SET @WP = cast(SUBSTRING(@Weights, @I,1)as int) * cast(SUBSTRING(@subno, @I, 1) as int)
IF(@WP > 9)
BEGIN
SET @A = SUBSTRING(CAST(@WP AS VARCHAR),1,1)
SET @B = SUBSTRING(CAST(@WP AS VARCHAR),2,1)
SET @WP = CAST(@A AS INT) + CAST(@B AS INT)
END

SET @WD = @WP + @WD
SET @I = @I + 1
END

SET @R = @WD % 10

IF(@R <> 0)
SET @WR = 10 - @R
ELSE
SET @WR = @R

IF(@WR <> CAST(@WA AS INT))
BEGIN
SET @result = 1 -- INVALID
END
ELSE
BEGIN
SET @result = 0 -- VALID
END
END
END
ELSE IF (LEN(@subno) = 10)
BEGIN
SET @I =1
SET @WD = 0
SET @Weights = '121212121'
SET @WA = SUBSTRING(@subno,10,1)
WHILE(@I < 10)
BEGIN
SET @WP = CAST(SUBSTRING(@Weights, @I, 1)AS INT) * CAST(SUBSTRING(@subno, @I, 1) AS INT)
IF(@WP > 9)
BEGIN
SET @A = SUBSTRING(CAST(@WP AS VARCHAR),1,1)
SET @B = SUBSTRING(CAST(@WP AS VARCHAR),2,1)
SET @WP = CAST(@A AS INT) + CAST(@B AS INT)
END

SET @WD = @WP + @WD
SET @I = @I + 1
END

SET @R = @WD % 10

IF(@R <> 0)
SET @WR = 10 - @R
ELSE
SET @WR = @R

IF (@WR<> @WA)
BEGIN
SET @result = 1 -- INVALID
END
ELSE
BEGIN
SET @result = 0 -- VALID
END
END
ELSE
SET @result = 1 -- INVALID
END
``````

Split the values which u get . Then iterate iver each side and add them. Please see the sample below.

``````declare @v varchar (16) , @num1 varchar(20) , @num2 varchar(20)
set @v = '1234567.78906656'

select  @num1 = substring(@v,0,charindex('.',@v))

select @num2 = substring(@v,charindex('.',@v)+1,len(@v))
--select @num1 = convert(int, substring(@v,0,charindex('.',@v)))
--select @num2 =  substring(@v,charindex('.',@v)+1,len(@v))
declare @index int = 1 ,@len INT , @char CHAR
declare @TotalOddL int  = 0
declare @TotalEvenL int  = 0
DECLARE @FullTotL INT = 0
declare @TotalOddR int  = 0
declare @TotalEvenR int  = 0
DECLARE @FullTotR INT = 0
DECLARE @TEMP INT
set @len= LEN(@num1)

WHILE @index <= @len
BEGIN
set @char = SUBSTRING(@num1, @index, 1)
SET @TEMP = cast(@char as int)
IF(@TEMP % 2 = 0)
SET  @TotalEvenL = @TotalEvenL + @char
else
SET  @TotalOddL = @TotalOddL + @char

SET @FullTotL = @TotalEvenL + @TotalOddL

SET @index= @index+ 1
END

Select 'LeftSide total' , @FullTotL
Select 'Left Side odd' , @TotalOddL
Select 'Left Side Even' , @TotalEvenL

SET @index = 1
set @len= LEN(@num2)
WHILE @index <= @len
BEGIN
set @char = SUBSTRING(@num2, @index, 1)
SET @TEMP = cast(@char as int)
IF(@TEMP % 2 = 0)
SET  @TotalEvenR= @TotalEvenR + @char
else
SET  @TotalOddR = @TotalOddR + @char

SET @FullTotR = @TotalEvenR + @TotalOddR

SET @index= @index+ 1
END

select 'TotalRSide' , @FullTotR
select 'RsideOdd' , @TotalOddR
select 'RSideEven' , @TotalEvenR

select 'Multiplied value' , @FullTotR * @FullTotL
``````
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download