TSQL--验证身份证是否有效

/****** Object:  UserDefinedFunction [dbo].[udf_IsvalidIDCard]    Script Date: 02/27/2014 16:03:20 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE FUNCTION [dbo].[udf_IsvalidIDCard]
(
@IDCardNo VARCHAR(50)=''
)
RETURNS BIT
AS
/*******************************************************************
函数名称：udf_IsvalidIDCard()
参数:@IDCardNo string 身份证号码
返回值：  bit 是否有效
功能描述：判断身份证号码是否合法

备注：目前中国的身份证号码有18位和15位.
1，18位身份证号码的组成:6位地区编码＋8位出生年月日＋3位编号（奇男偶女）＋1位校验码
2，15位身份证号码的组成:6位地区编码＋6位出生年月日＋3位编号（奇男偶女）
*******************************************************************/
BEGIN

DECLARE @Length INT, 
@Loop INT, 
@Sum INT
DECLARE @SingleChar CHAR

SET @Sum = 0
IF @IDCardNo IS NULL OR @IDCardNo = NULL OR LTRIM(RTRIM(@IDCardNo)) = ''
BEGIN
RETURN 0
END

SET @Length = LEN(@IDCardNo)
--判断位数
IF @Length < > 18 AND @Length < > 15
BEGIN
RETURN 0    
END
IF @Length = 18
BEGIN
IF ISNUMERIC(LEFT(@IDCardNo, 17)) = 0
BEGIN    
RETURN 0
END
IF ISDATE(SUBSTRING(@IDCardNo, 7, 4) + '-' + SUBSTRING(@IDCardNo, 11, 2) + '-' + SUBSTRING(@IDCardNo, 13, 2)) = 0 
BEGIN
RETURN 0
END
SET @Loop = 17
WHILE (@Loop  >= 1)
BEGIN
SET @Sum = @Sum + CONVERT(INT,SUBSTRING(@IDCardNo, @Loop, 1)) * (POWER(2,(18 - @Loop)) % 11)
SET @Loop = @Loop - 1
END
SET @Loop = @Sum % 11
IF @Loop = 0
       BEGIN
SET @SingleChar = '1'
END
      ELSE IF @Loop = 1
BEGIN
SET @SingleChar = '0'
END
      ELSE IF @Loop = 2
BEGIN
SET @SingleChar = 'X'
END
      ELSE
BEGIN
SET @SingleChar = CONVERT(VARCHAR(2),(12 - @Loop))
END
IF LOWER(RIGHT(@IDCardNo, 1)) < > LOWER(@SingleChar)
BEGIN
RETURN 0
END
END
ELSE IF @Length = 15
BEGIN
IF ISNUMERIC(@IDCardNo) = 0
BEGIN
RETURN 0
END    
IF ISDATE('19' + SUBSTRING(@IDCardNo, 7, 2) + '-' + SUBSTRING(@IDCardNo, 9, 2) + '-' + SUBSTRING(@IDCardNo, 11, 2)) = 0 
BEGIN
RETURN 0
END
END

RETURN 1

END
GO