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).
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