SQL SCRIPT TO Validate South African ID Number

CREATE PROCEDURE spValidateID(@Id numeric)

AS

BEGIN

DECLARE @idlength INT;
DECLARE @gender INT;

DECLARE @country INT;

DECLARE @cnt int

DECLARE @Control1 int

DECLARE @cnt2 int

DECLARE @Control2 int

DECLARE @Control3 int

DECLARE @control4 INT



SET @cnt =1

SET @Control1=0

SET @idlength = LEN(@Id)

SET @gender = SUBSTRING(CAST(@Id as VARCHAR),7,1)

SET @country = SUBSTRING(CAST(@Id as VARCHAR),11,1)

--Add all the digits in the odd positions (excluding last digit)

WHILE @cnt<13

BEGIN

SET @Control1 = @Control1 + CAST(SUBSTRING(CAST(@Id as VARCHAR),@cnt,1)AS INT)

SET @cnt = @cnt + 2

END
--Move the even positions into a field and multiply the number by 2

SET @Control2= 2*CAST(SUBSTRING(CAST(@Id as VARCHAR),2,1) +

SUBSTRING(CAST(@Id as VARCHAR),4,1) +

SUBSTRING(CAST(@Id as VARCHAR),6,1) +

SUBSTRING(CAST(@Id as VARCHAR),8,1) +

SUBSTRING(CAST(@Id as VARCHAR),10,1)+

SUBSTRING(CAST(@Id as VARCHAR),12,1) AS INT)
--Add the digits of the result of @Control2
SET @cnt2 = 1

SET @Control3=0
WHILE @cnt2<=LEN(@Control2)

BEGIN

SET @Control3=@Control3 + SUBSTRING(CAST(@Control2 as VARCHAR),@cnt2,1)

SET @cnt2 = @cnt2 + 1

END
--Add the results in @Control3 to the results in @Control1
SET @control4= @Control1 + @Control3
--subtract the second digit from 10

--The number must tally with the last number in the ID Number

--If the answer differs, the ID number is invalid

IF @idlength=13 AND SUBSTRING(CAST(@Id as VARCHAR),13,1)=(SELECT 10-CAST(SUBSTRING(CAST(@control4 AS VARCHAR),2,1)AS INT))

BEGIN

IF @gender < 5

BEGIN

IF @country <> 0

BEGIN

SELECT @id as ID,'Female' as Gender,'Other' as Citizenship
END
ELSE
SELECT @id as ID,'Female' as Gender,'south african' as Citizenship
END
ELSE IF @gender > 4 
BEGIN

IF @country <> 0

BEGIN

SELECT @id as ID,'Male' as Gender,'Other' as Citizenship

END

ELSE

SELECT @id as ID,'Male' as Gender,'south african' as Citizenship

END

END

ELSE
SELECT @id,'Not south african ID'

END

How to check table foreign key in sql

Use KIWI;

GO

SELECT f.name AS ForeignKey,

SCHEMA_NAME(f.SCHEMA_ID) SchemaName,

OBJECT_NAME(f.parent_object_id) AS TableName,

COL_NAME(fc.parent_object_id,fc.parent_column_id) AS ColumnName,

SCHEMA_NAME(o.SCHEMA_ID) ReferenceSchemaName,

OBJECT_NAME (f.referenced_object_id) AS ReferenceTableName,

COL_NAME(fc.referenced_object_id,fc.referenced_column_id) AS ReferenceColumnName

FROM sys.foreign_keys AS f

INNER JOIN sys.foreign_key_columns AS fc

ON f.OBJECT_ID = fc.constraint_object_id

INNER JOIN sys.objects AS o

ON o.OBJECT_ID = fc.referenced_object_id

WHERE OBJECT_NAME(f.parent_object_id) = 'tblCandidateAdaptSend'