I have developed a stored procedure in SQL to verify if a 13 digit national ID number is valid or not. At the moment the procedure works fine but I can only verify 1 number at a time. Procedure returns 1 if valid and 0 if not. Is the a way I can automate this so that I can check a lot of ID numbers rather than doing it manually.
The stored procedure I have written is shown below.
CREATE PROCEDURE [dbo].[verifyRSAIDs]
@idNumber VARCHAR(13), -- ID number to be checked
@isValid INT OUTPUT -- Will return 0 if ID number is not valid and 1 otherwise
AS
BEGIN
SET NOCOUNT OFF
DECLARE @digit1 int, --holds the 1st digit
@digit2 int, --holds the 2nd digit
@digit3 int, --holds the 3rd digit
@digit4 int, --holds the 4th digit
@digit5 int, --holds the 5th digit
@digit6 int, --holds the 6th digit
@digit7 int, --holds the 7th digit
@digit8 int, --holds the 8th digit
@digit9 int, --holds the 9th digit
@digit10 int, --holds the 10th digit
@digit11 int, --holds the 11th digit
@digit12 int, --holds the 12th digit
@digit13 int --holds the 13th digit
SET @digit1 = substring(@idNumber, 1 , 1) --Extract all the digits
SET @digit2 = substring(@idNumber, 2 , 1)
SET @digit3 = substring(@idNumber, 3 , 1)
SET @digit4 = substring(@idNumber, 4 , 1)
SET @digit5 = substring(@idNumber, 5 , 1)
SET @digit6 = substring(@idNumber, 6 , 1)
SET @digit7 = substring(@idNumber, 7 , 1)
SET @digit8 = substring(@idNumber, 8 , 1)
SET @digit9 = substring(@idNumber, 9 , 1)
SET @digit10 = substring(@idNumber, 10 , 1)
SET @digit11 = substring(@idNumber, 11 , 1)
SET @digit12 = substring(@idNumber, 12 , 1)
SET @digit13 = substring(@idNumber, 13 , 1)
--Multiple every second digit from the right most by 2
--If the result is greater than 10 the new digit is the sum of the two digits
--Digit 12
SET @digit12 = @digit12 * 2
IF @digit12 > 9
SET @digit12 = @digit12 - 9
--Digit 10
SET @digit10 = @digit10 * 2
IF @digit10 > 9
SET @digit10 = @digit10 - 9
--Digit 8
SET @digit8 = @digit8 * 2
IF @digit8 > 9
SET @digit8 = @digit8 - 9
--Digit 6
SET @digit6 = @digit6 * 2
IF @digit6 > 9
SET @digit6 = @digit6 - 9
--Digit 4
SET @digit4 = @digit4* 2
IF @digit4 > 9
SET @digit4 = @digit4 - 9
--Digit 2
SET @digit2 = @digit2 * 2
IF @digit2 > 9
SET @digit2 = @digit2 - 9
DECLARE @sum as int --Will hold the sum of all the digits
DECLARE @result as int --Will hold the result of modulo 10 division
--Add all the new digits
-- If the modulo 10 of the sum is zero then the ID number is valid
SET @sum = @digit1 + @digit2 + @digit3 + @digit4 +
@digit5 + @digit6 + @digit7 + @digit8 +
@digit9 +
@digit10 +
@digit11 +
@digit12 +
@digit13
SET @result = @sum % 10
if @result = 0
SET @isValid = 1 --Valid ID number
else
SET @isValid = 0 --Invalid ID number
END
--I am calling the proceedure as follows with an example id number:
DECLARE @check as INT
EXEC verifyRSAIDs
@idNumber = '0002131000008',
@isValid = @check OUTPUT
SELECT @check AS 'ID Valid'
--I want to run the procedure on the following ID numbers:
('0002131000008'),('9910260000006'),('9907089000001'),('9903208000006'),('9811307000001'),
('9808126000006'),('null'),
('9905292000002'),('9702081000004'),('9610210000003'),('9607039000008'),('9603158000002'),
('9511267000005'),('9504205000005'),
('9412314000009'),('9409123000006'),('9405252000007'),('9310170000008'),('9306299000002'),
('9303118000008'),('9211217000003'),
('9208036000008'),('9204155000002'),('9112274000003'),('9105212000002'),('9101311000001'),
('9010130000003'),('9006259000006'),
('9003078000002'),('8911177000007'),('8907306000009'),('8904115000007'),('8809033000007'),
('8805162000008'),('8801271000005'),
('8710090000007'),('8706219000001'),('8703038000007'),('8611137000002'),('8607266000003'),
('8604075000001'),('8512184000004'),
('8508303000008'),('8505122000003'),('8501221000002'),('8413040000005'),('8406169000008'),
('8402278000005'),('8311097000007'),
('8307226000009'),('8304035000007'),('8208263000003'),('8205082000008'),('8201181000007'),
('8109300000008'),('8106129000003'),
('8102228000002'),('8011047000004'),('8007176000005'),('7912104000004'),('7908223000008'),
('7905042000003'),('7901141000002'),
('7809260000002'),('7806089000007'),('7802188000006'),('7710317000005'),('7703255000004'),
('7608173000004'),('7604292000009'),
('7601101000006'),('7509220000007'),('7506049000000'),('7502148000001'),('7407096000005'),
('7312014000006'),('7304252000004'),
('7301051000003'),('7209170000004'),('7205309000006'),('7202108000006'),('7110237000005'),
('7107056000009'),('7103175000004');
Just make that a Scalar-Valued Function, instead of a Stored Procedure. EG:\
CREATE or ALTER Function [dbo].[IsValueRSAIDs]( @idNumber VARCHAR (13) ) --ID number to be checked
returns INT --Will return 0 if ID number is not valid and 1 otherwise
AS
BEGIN
DECLARE @isValid int = 0;
if (ISNUMERIC(@idNumber) <> 1 or DATALENGTH(@idNumber) <> 13 )
return 0;
DECLARE @digit1 int, --holds the 1st digit
@digit2 int, --holds the 2nd digit
@digit3 int, --holds the 3rd digit
@digit4 int, --holds the 4th digit
@digit5 int, --holds the 5th digit
@digit6 int, --holds the 6th digit
@digit7 int, --holds the 7th digit
@digit8 int, --holds the 8th digit
@digit9 int, --holds the 9th digit
@digit10 int, --holds the 10th digit
@digit11 int, --holds the 11th digit
@digit12 int, --holds the 12th digit
@digit13 int --holds the 13th digit
SET @digit1 = substring(@idNumber, 1 , 1) --Extract all the digits
SET @digit2 = substring(@idNumber, 2 , 1)
SET @digit3 = substring(@idNumber, 3 , 1)
SET @digit4 = substring(@idNumber, 4 , 1)
SET @digit5 = substring(@idNumber, 5 , 1)
SET @digit6 = substring(@idNumber, 6 , 1)
SET @digit7 = substring(@idNumber, 7 , 1)
SET @digit8 = substring(@idNumber, 8 , 1)
SET @digit9 = substring(@idNumber, 9 , 1)
SET @digit10 = substring(@idNumber, 10 , 1)
SET @digit11 = substring(@idNumber, 11 , 1)
SET @digit12 = substring(@idNumber, 12 , 1)
SET @digit13 = substring(@idNumber, 13 , 1)
--Multiple every second digit from the right most by 2
--If the result is greater than 10 the new digit is the sum of the two digits
--Digit 12
SET @digit12 = @digit12 * 2
if @digit12 > 9
SET @digit12 = @digit12 - 9
--Digit 10
SET @digit10 = @digit10 * 2
if @digit10 > 9
SET @digit10 = @digit10 - 9
--Digit 8
SET @digit8 = @digit8 * 2
if @digit8 > 9
SET @digit8 = @digit8 - 9
--Digit 6
SET @digit6 = @digit6 * 2
if @digit6 > 9
SET @digit6 = @digit6 - 9
--Digit 4
SET @digit4 = @digit4* 2
if @digit4 > 9
SET @digit4 = @digit4 - 9
--Digit 2
SET @digit2 = @digit2 * 2
if @digit2 > 9
SET @digit2 = @digit2 - 9
DECLARE @sum as int --Will hold the sum of all the digits
DECLARE @result as int --Will hold the result of modulo 10 division
--Add all the new digits
-- If the modulo 10 of the sum is zero then the ID number is valid
SET @sum = @digit1 +
@digit2 +
@digit3 +
@digit4 +
@digit5 +
@digit6 +
@digit7 +
@digit8 +
@digit9 +
@digit10 +
@digit11 +
@digit12 +
@digit13
SET @result = @sum % 10
if @result = 0
SET @isValid = 1 --Valid ID number
else
SET @isValid = 0 --Invalid ID number
return @isValid;
END
go
select val, dbo.IsValueRSAIDs(val)
from (values
('0002131000008'),('9910260000006'),('9907089000001'),('9903208000006'),('9811307000001'),
('9808126000006'),('null'),
('9905292000002'),('9702081000004'),('9610210000003'),('9607039000008'),('9603158000002'),
('9511267000005'),('9504205000005'),
('9412314000009'),('9409123000006'),('9405252000007'),('9310170000008'),('9306299000002'),
('9303118000008'),('9211217000003'),
('9208036000008'),('9204155000002'),('9112274000003'),('9105212000002'),('9101311000001'),
('9010130000003'),('9006259000006'),
('9003078000002'),('8911177000007'),('8907306000009'),('8904115000007'),('8809033000007'),
('8805162000008'),('8801271000005'),
('8710090000007'),('8706219000001'),('8703038000007'),('8611137000002'),('8607266000003'),
('8604075000001'),('8512184000004'),
('8508303000008'),('8505122000003'),('8501221000002'),('8413040000005'),('8406169000008'),
('8402278000005'),('8311097000007'),
('8307226000009'),('8304035000007'),('8208263000003'),('8205082000008'),('8201181000007'),
('8109300000008'),('8106129000003'),
('8102228000002'),('8011047000004'),('8007176000005'),('7912104000004'),('7908223000008'),
('7905042000003'),('7901141000002'),
('7809260000002'),('7806089000007'),('7802188000006'),('7710317000005'),('7703255000004'),
('7608173000004'),('7604292000009'),
('7601101000006'),('7509220000007'),('7506049000000'),('7502148000001'),('7407096000005'),
('7312014000006'),('7304252000004'),
('7301051000003'),('7209170000004'),('7205309000006'),('7202108000006'),('7110237000005'),
('7107056000009'),('7103175000004') ) v(val);