samedi 19 octobre 2019

Failed to Make Modulo11 Function on SQL Server

Create a function CheckModulo11, that checks if a given accountNr is valid number use this function in a check clause in a table with a column that represents an accountNr. (You need to create a table to store the accountNr by adding constrain and try to insert data into the table).

Question

Error Message

My Code:

create function CheckModulo11(@accountNr int)
returns @tableModulo11 table(
    modulo_nr int,
    number int,
    total int,
    status char(5)
)
as
BEGIN
    Declare @tmpTable table(
        modulo_nr int,
        number int,
        total int,
        status char(5)
    )
    Declare @length int, @counter int = 1, @value int, @total int, @changeNums char(10);
    SET @changeNums = CONVERT(char(10), @accountNr);
    Set @length = LEN(@changeNums);
    while @length > 0
    BEGIN
        SET @value = CONVERT(int,SUBSTRING(@changeNums, @length, 1))
        insert into @tmpTable
        (modulo_nr, number, total, status) values
        (@counter, @value, (@counter * @value), '-');
        SET @total = @total + (@counter * @value);
        SET @length = @length - 1;
        SET @counter = @counter + 1; 
    END
    IF @total % 11 = 0
        insert into @tmpTable (modulo_nr, number, total, status) values ('', '', @total, 'valid');
    ELSE
        insert into @tmpTable (modulo_nr, number, total, status) values ('', '', @total, 'not valid');

insert into @tableModulo11
SELECT * FROM @tmpTable
return
END
GO
select * from dbo.CheckModulo11(972428577);

Aucun commentaire:

Enregistrer un commentaire