sql-server NIN validation function

علی ذوالفقار
1402/05/09 10:58:04 (119)
 ALTER FUNCTION dbo.fn_isNin	( @NationalCode VARCHAR(10) )
-- RETURNS NVARCHAR(100)
RETURNS BIT
AS
BEGIN
	DECLARE @ErrorMessage VARCHAR(100)
		,@RV BIT
		,@C INT
		,@N INT
		,@R INT
	SET @ErrorMessage = ''
	SET @RV = 0 
	IF ( LEN(@NationalCode) <> 10 OR @NationalCode = '0000000000' OR @NationalCode = '1111111111' OR @NationalCode = '2222222222' OR @NationalCode = '3333333333' OR @NationalCode = '4444444444' OR @NationalCode = '5555555555' OR @NationalCode = '6666666666' OR @NationalCode = '7777777777' OR @NationalCode = '8888888888' OR @NationalCode = '9999999999' )
		BEGIN
			SET @ErrorMessage = 'کد ملی نامعتبر است'
			SET @RV = 0 
		END
	ELSE
		BEGIN
			SET @C = cast(SUBSTRING(@NationalCode, 10, 1) as int)
			SET @N = (cast(SUBSTRING(@NationalCode, 1, 1) as int) * 10) + (cast(SUBSTRING(@NationalCode, 2, 1) as int) * 9) + (cast(SUBSTRING(@NationalCode, 3, 1) as int) * 8) + (cast(SUBSTRING(@NationalCode, 4, 1) as int) * 7) + (cast(SUBSTRING(@NationalCode, 5, 1) as int) * 6) + (cast(SUBSTRING(@NationalCode, 6, 1) as int) * 5) + (cast(SUBSTRING(@NationalCode, 7, 1) as int) * 4) + (cast(SUBSTRING(@NationalCode, 8, 1) as int) * 3) + (cast(SUBSTRING(@NationalCode, 9, 1) as int) * 2)
			SET @R = @N % 11
			IF ((@R = 0 AND @R = @C) OR (@R = 1 AND @C = 1) OR (@R > 1 AND @C = 11 - @R))
				BEGIN
					SET @ErrorMessage = 'کد ملی معتبر است'
					SET @RV = 1 
				END 
			ELSE
				BEGIN 
					SET @ErrorMessage = 'کد ملی نامعتبر است'
					SET @RV = 0 
				END 
		END

	--RETURN @ErrorMessage
	RETURN @RV
	/*
	NOTE : 
		you can change < RETURNS BIT > to < RETURNS NVARCHAR(100) > on top of the script 
		and change < RETURN @RV > to < RETURN @ErrorMessage >
		to alter this function to return an error-message instead of a bit value 
	*/
END       
Back