Hi clever people
I've created the following procedure below that, when I enter a SA ID number (SSN in the west I imagine) it tells me whether the number entered is false or not.
Below the procedure is a table creation where I enter a row that includes the Idnumber column whére I enter the number as described above.
What I need help on is when I enter the ID number into the table the procedure must verify if the number entered is correct or not. I'm stuck here a bit so if someone can help me I really appreciate it.
Thanks guys!
CREATE PROCEDURE [dbo].[ValidateSAIDNumber]
@IDNumber VARCHAR (13)
AS
BEGIN
DECLARE @a INT -- ~Holds Negative Index numbers
DECLARE @b-2 INT -- ~Holds Positive Index numbers
DECLARE @C INT
DECLARE @D INT -- ~The check digit
DECLARE @I INT -- ~Counter Variable
SET @D = -1
SET @a = 0
SET @b-2 = 0
BEGIN TRY
--PRINT 'Getting Negative Numbers____________________'
SET @I = 0
WHILE (@I < 6)
BEGIN
--PRINT SUBSTRING(@IDNumber, 2 * @I + 1, 1)
SET @a = @a + CAST(SUBSTRING(@IDNumber, 2 * @I + 1, 1) AS INT)
SET @I = @I + 1
END --WHILE
SET @I = 1
WHILE (@I < 7)
BEGIN
SET @b-2 = @b-2 * 10 + CAST(SUBSTRING(@IDNumber, 2 * @I, 1) AS INT)
SET @I = @I + 1
END --WHILE
SET @C = 0
WHILE (@B > 0)
BEGIN
END --WHILE
SET @D = 10 - (@C % 10)
IF (@D = 10)
SET @D = 0
IF (CAST(SUBSTRING(@IDNumber, 13, 1) AS INT) = @D)
BEGIN
PRINT @IDNumber
RETURN 1
END
ELSE
BEGIN
PRINT 'Invalid ID Number'
RETURN 0
END
END TRY
BEGIN CATCH
RETURN 0
END CATCH
RETURN 0
END
*******Table creation*******
CREATE TABLE Clientdataset (
Idnumber VARCHAR(13) DEFAULT NULL,
FirstName varchar(50) DEFAULT NULL,
Surname varchar(50) DEFAULT NULL,
DateOfBirth date DEFAULT NULL,
FolderNumber varchar(20) DEFAULT NULL,
Gender VARCHAR(9) NULL
CHECK(Gender IN ('Male', 'Female', 'Unknown')),
RecordNumber int NOT NULL IDENTITY(1,1)
PRIMARY KEY,
) ON [PRIMARY]
INSERT INTO Clientdataset(Idnumber,FirstName,Surname,DateOfBirth,
FolderNumber,Gender)
VALUES('6809115290084', 'Robert', 'Sidney', '1968-09-11', 'RS01','Male')
SELECT * FROM Clientdataset
January 30, 2021 at 3:33 am
I changed to using descriptive variable names. And I added some debugging displays to help you work through any issues. The sample number you provided is failing the test, but I'm not sure if that's legit / what may be wrong the code. Hopefully the debug displays will help you figure that out.
SET ANSI_NULLS ON;
SET QUOTED_IDENTIFIER ON;
GO
ALTER PROCEDURE [dbo].[ValidateSAIDNumber]
@counterDNumber VARCHAR (13)
AS
BEGIN
DECLARE @check_digit INT
DECLARE @check_total INT
DECLARE @counter INT
DECLARE @neg_index_number int
DECLARE @pos_index_number INT
SET @check_digit = -1
SET @neg_index_number = 0
SET @pos_index_number = 0
BEGIN TRY
--PRINT 'Getting Negative Numbers____________________'
SET @neg_index_number = CAST(SUBSTRING(@counterDNumber, 3, 1) AS int) +
CAST(SUBSTRING(@counterDNumber, 5, 1) AS int) + CAST(SUBSTRING(@counterDNumber, 7, 1) AS int) +
CAST(SUBSTRING(@counterDNumber, 9, 1) AS int) + CAST(SUBSTRING(@counterDNumber,11, 1) AS int) +
CAST(SUBSTRING(@counterDNumber,13, 1) AS int)
--PRINT 'Getting Positive Numbers____________________'
SET @counter = 1
WHILE (@counter < 7)
BEGIN
SET @pos_index_number = @pos_index_number * 10 + CAST(SUBSTRING(@counterDNumber, 2 * @counter, 1) AS INT)
SELECT 'debug1', @counter AS loop#, @neg_index_number AS neg_index, @pos_index_number AS pos_index, @check_total AS c, @check_digit AS check_digit
SET @counter = @counter + 1
END --WHILE
SET @pos_index_number = @pos_index_number * 2
SELECT 'debug2', @pos_index_number AS pos_index, @check_total AS c, @check_digit AS check_digit
SET @check_total = 0
WHILE (@pos_index_number > 0)
BEGIN
SET @check_total = @check_total + @pos_index_number % 10
SET @pos_index_number = @pos_index_number / 10
END --WHILE
SET @check_total = @check_total + @neg_index_number
SET @check_digit = 10 - (@check_total % 10)
IF (@check_digit = 10)
SET @check_digit = 0
SELECT 'debug3', @neg_index_number AS neg_index, @pos_index_number AS pos_index, @check_total AS c, @check_digit AS check_digit
IF (CAST(SUBSTRING(@counterDNumber, 13, 1) AS INT) = @check_digit)
BEGIN
PRINT @counterDNumber
RETURN 1
END
ELSE
BEGIN
PRINT 'Invalid ID Number'
RETURN 0
END
END TRY
BEGIN CATCH
RETURN 0
END CATCH
RETURN 0
END
SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.
January 30, 2021 at 5:01 am
Hi Scott
Thank you very much it's very helpful. This is the however the actual problem. I create a table and enter the following values:
CREATE TABLE Clientdataset (
Idnumber VARCHAR(13) DEFAULT NULL,
FirstName varchar(50) DEFAULT NULL,
Surname varchar(50) DEFAULT NULL,
DateOfBirth date DEFAULT NULL,
FolderNumber varchar(20) DEFAULT NULL,
Gender VARCHAR(9) NULL
CHECK(Gender IN ('Male', 'Female', 'Unknown')),
RecordNumber int NOT NULL IDENTITY(1,1)
PRIMARY KEY,
) ON [PRIMARY]
INSERT INTO Clientdataset(Idnumber,FirstName,Surname,DateOfBirth,
FolderNumber,Gender)
VALUES('6809115290084', 'Robert', 'Sidney', '1968-09-11', 'RS01','Male')
How do I let the procedure check if the Idnumber column entered is correct or not as per the procedure and if incorrect roll back and have the Idnumber re-entered?
Thanks for your help I really appreciate it.
Kind regards
Hi guys
Worked out a possible solution. what do you think? Any other, better ideas are welcome.
Kind regards
BEGIN TRANSACTION
INSERT INTO Clientdataset(Idnumber,FirstName,Surname,DateOfBirth,
FolderNumber,Gender)
VALUES('6906275293084', 'Robert', 'Sidney', '1968-09-11', 'RS01','Male')
DECLARE @IDNumber VARCHAR(13),
@Returned BIT;
SET @IDNumber = (SELECT Idnumber FROM Clientdataset
WHERE RecordNumber = SCOPE_IDENTITY())
--SELECT @@IDENTITY, SCOPE_IDENTITY(), @IDNumber
EXEC [dbo].[ValidateSAIDNumber] @IDNumber, @Return = @Returned OUTPUT
--SELECT @Returned
IF @Returned = 1
BEGIN
PRINT 'Valid ID Number ' + @IDNumber
COMMIT
END
ELSE
BEGIN
PRINT 'Invalid ID Number'
ROLLBACK
END
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply