SQL Server stored procedure: finding the values of Odd and even
<p>I'm using <code>@subno</code> 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? </p>

<p>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. </p>

<p>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".</p>

<p>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.</p>

<p>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</p>

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

<p>Please see below my sample work</p>

<pre><code>-- 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&lt;9
        BEGIN
            SET @WP = cast(SUBSTRING(@Weights, @I,1)as int) * cast(SUBSTRING(@subno, @I, 1) as int)
            IF(@WP &gt; 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 &lt;&gt; 0)
            SET @WR = 10 - @R
        ELSE
            SET @WR = @R

        IF(@WR &lt;&gt; 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 &lt; 10)
    BEGIN
        SET @WP = CAST(SUBSTRING(@Weights, @I, 1)AS INT) * CAST(SUBSTRING(@subno, @I, 1) AS INT)
        IF(@WP &gt; 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 &lt;&gt; 0)
        SET @WR = 10 - @R
    ELSE
        SET @WR = @R

    IF (@WR&lt;&gt; @WA)
    BEGIN
        SET @result = 1 -- INVALID
    END
    ELSE
    BEGIN
        SET @result = 0 -- VALID
    END
END
ELSE
    SET @result = 1 -- INVALID
END
</code></pre>