SQLSERVER校验身份证号 合法性

最近在做一个项目时需要在存储过程中判断身份证号的参数的合法性，因此参照网上对应的C++代码改造成SQL SERVER的函数

C++代码的链接：http://blog.chinaunix.net/uid-27105712-id-4233792.html

CREATE TABLE [dbo].[days] (
[nmonth] int NULL ,
[daynum] int NULL ,
[leap] int NULL
)

INSERT INTO [dbo].[days] ([nmonth], [daynum], [leap]) VALUES (N'1', N'31', N'0')
INSERT INTO [dbo].[days] ([nmonth], [daynum], [leap]) VALUES (N'2', N'28', N'0')
INSERT INTO [dbo].[days] ([nmonth], [daynum], [leap]) VALUES (N'3', N'31', N'0')
INSERT INTO [dbo].[days] ([nmonth], [daynum], [leap]) VALUES (N'4', N'30', N'0')
INSERT INTO [dbo].[days] ([nmonth], [daynum], [leap]) VALUES (N'5', N'31', N'0')
INSERT INTO [dbo].[days] ([nmonth], [daynum], [leap]) VALUES (N'6', N'30', N'0')
INSERT INTO [dbo].[days] ([nmonth], [daynum], [leap]) VALUES (N'7', N'31', N'0')
INSERT INTO [dbo].[days] ([nmonth], [daynum], [leap]) VALUES (N'8', N'31', N'0')
INSERT INTO [dbo].[days] ([nmonth], [daynum], [leap]) VALUES (N'9', N'30', N'0')
INSERT INTO [dbo].[days] ([nmonth], [daynum], [leap]) VALUES (N'10', N'31', N'0')
INSERT INTO [dbo].[days] ([nmonth], [daynum], [leap]) VALUES (N'11', N'30', N'0')
INSERT INTO [dbo].[days] ([nmonth], [daynum], [leap]) VALUES (N'12', N'31', N'0')
INSERT INTO [dbo].[days] ([nmonth], [daynum], [leap]) VALUES (N'1', N'31', N'1')
INSERT INTO [dbo].[days] ([nmonth], [daynum], [leap]) VALUES (N'2', N'29', N'1')
INSERT INTO [dbo].[days] ([nmonth], [daynum], [leap]) VALUES (N'3', N'31', N'1')
INSERT INTO [dbo].[days] ([nmonth], [daynum], [leap]) VALUES (N'4', N'30', N'1')
INSERT INTO [dbo].[days] ([nmonth], [daynum], [leap]) VALUES (N'5', N'31', N'1')
INSERT INTO [dbo].[days] ([nmonth], [daynum], [leap]) VALUES (N'6', N'30', N'1')
INSERT INTO [dbo].[days] ([nmonth], [daynum], [leap]) VALUES (N'7', N'31', N'1')
INSERT INTO [dbo].[days] ([nmonth], [daynum], [leap]) VALUES (N'8', N'31', N'1')
INSERT INTO [dbo].[days] ([nmonth], [daynum], [leap]) VALUES (N'9', N'30', N'1')
INSERT INTO [dbo].[days] ([nmonth], [daynum], [leap]) VALUES (N'10', N'31', N'1')
INSERT INTO [dbo].[days] ([nmonth], [daynum], [leap]) VALUES (N'11', N'30', N'1')
INSERT INTO [dbo].[days] ([nmonth], [daynum], [leap]) VALUES (N'12', N'31', N'1')

CREATE TABLE [dbo].[VI] (
[num] int NOT NULL IDENTITY(1,1) ,
[alphet] char(1) NULL
)
SET IDENTITY_INSERT [dbo].[VI] ON
INSERT INTO [dbo].[VI] ([num], [alphet]) VALUES (N'1', N'1')
INSERT INTO [dbo].[VI] ([num], [alphet]) VALUES (N'2', N'0')
INSERT INTO [dbo].[VI] ([num], [alphet]) VALUES (N'3', N'X')
INSERT INTO [dbo].[VI] ([num], [alphet]) VALUES (N'4', N'9')
INSERT INTO [dbo].[VI] ([num], [alphet]) VALUES (N'5', N'8')
INSERT INTO [dbo].[VI] ([num], [alphet]) VALUES (N'6', N'7')
INSERT INTO [dbo].[VI] ([num], [alphet]) VALUES (N'7', N'6')
INSERT INTO [dbo].[VI] ([num], [alphet]) VALUES (N'8', N'5')
INSERT INTO [dbo].[VI] ([num], [alphet]) VALUES (N'9', N'4')
INSERT INTO [dbo].[VI] ([num], [alphet]) VALUES (N'10', N'3')
INSERT INTO [dbo].[VI] ([num], [alphet]) VALUES (N'11', N'2')
SET IDENTITY_INSERT [dbo].[VI] OFF

CREATE TABLE [dbo].[WI] (
[num] int NOT NULL IDENTITY(1,1) ,
[w] int NULL
)

SET IDENTITY_INSERT [dbo].[WI] ON
INSERT INTO [dbo].[WI] ([num], [w]) VALUES (N'1', N'7')
INSERT INTO [dbo].[WI] ([num], [w]) VALUES (N'2', N'9')
INSERT INTO [dbo].[WI] ([num], [w]) VALUES (N'3', N'10')
INSERT INTO [dbo].[WI] ([num], [w]) VALUES (N'4', N'5')
INSERT INTO [dbo].[WI] ([num], [w]) VALUES (N'5', N'8')
INSERT INTO [dbo].[WI] ([num], [w]) VALUES (N'6', N'4')
INSERT INTO [dbo].[WI] ([num], [w]) VALUES (N'7', N'2')
INSERT INTO [dbo].[WI] ([num], [w]) VALUES (N'8', N'1')
INSERT INTO [dbo].[WI] ([num], [w]) VALUES (N'9', N'6')
INSERT INTO [dbo].[WI] ([num], [w]) VALUES (N'10', N'3')
INSERT INTO [dbo].[WI] ([num], [w]) VALUES (N'11', N'7')
INSERT INTO [dbo].[WI] ([num], [w]) VALUES (N'12', N'9')
INSERT INTO [dbo].[WI] ([num], [w]) VALUES (N'13', N'10')
INSERT INTO [dbo].[WI] ([num], [w]) VALUES (N'14', N'5')
INSERT INTO [dbo].[WI] ([num], [w]) VALUES (N'15', N'8')
INSERT INTO [dbo].[WI] ([num], [w]) VALUES (N'16', N'4')
INSERT INTO [dbo].[WI] ([num], [w]) VALUES (N'17', N'2')
INSERT INTO [dbo].[WI] ([num], [w]) VALUES (N'18', N'1')
SET IDENTITY_INSERT [dbo].[WI] OFF

create function checkdate
(
@dt varchar(8)
)
returns int
as
BEGIN
    if(substring(@dt, 1, 4) < 1900)
    BEGIN
        --print '年份超出范围'
        return 0
    END
    declare @month int
    set @month = 0
    select @month = convert(int, substring(@dt, 5, 2))
    if(@month < 0 or @month > 12)
    BEGIN
    --print '月份超出范围'
        return 0
    END
    declare @leap int
    set @leap = 0
    declare @year int
    set @year = convert(int,substring(@dt, 1, 4))  
    set @month = convert(int,substring(@dt, 5, 2))
    declare @day int
    set @day = convert(int, substring(@dt, 7, 2))
    declare @day1 int
    set @day1 = -1
    if(((@year % 4 = 0) and (@year % 100 <> 0)) or (@year % 400 = 0))
    BEGIN
        set @leap = 1;       
    END
    select @day1 = daynum from days where leap = @leap and nmonth = @month
    if(@day > @day1 or @day < 0)
    BEGIN
        return 0
    END
   ELSE
   BEGIN
        return 1
   END
    return 0
END

create function CHECKSUM
(
@idcard varchar(18)
)
returns char
AS
BEGIN
    declare @rtn char
    set @rtn = ''
    declare @remaining int
    set @remaining = 0
    declare @ind int
    set @ind = 1
    declare @sum int
    set @sum = 1;
    while(@ind <= 17)
    BEGIN
      set @sum = @sum + (convert(int, substring(@idcard, @ind, 1))) * (select w from WI where num = @ind)
      set @ind = @ind + 1
    end
        set @remaining = @sum % 11
        select @rtn = alphet from VI where num = @remaining
    return @rtn
END

create function VerifyCertificateID
(
@idcard varchar(18),  --身份证号
@sex int  --0:男性  1:女性
)
returns int
as
BEGIN
    --空身份证号直接返回
    if(len(@idcard) = 0)
    BEGIN
        --print '空身份证号'
        return 0
    END
    --先将15位身份账号延伸到18位
    if(len(@idcard) = 15)
    BEGIN
        --在年前面不上19前缀
        set @idcard = SubString(@idcard,1,6) + '19' + SUBSTRING(@idcard, 7, len(@idcard) - 6);
        --重新设置最后一位的校验和
        set @idcard = @idcard + dbo.CHECKSUM(@idcard);
    END
    --如果还没到18位，错误
    if(len(@idcard) <> 18)
    BEGIN
        return 0
    END
    --1到17位必须是数字
    declare @ind int
    set @ind = 1
    while(@ind <= 17)
    BEGIN
        if(ascii(substring(@idcard, @ind, 1)) < ascii('0') or ascii(substring(@idcard, @ind, 1)) > ascii('9'))
        BEGIN
            --print '前17位有非数字'
            return 0
        END
        set @ind = @ind + 1
    END
    --检查第18位的校验和
    declare @sum char
    set @sum = ''
    select @sum = dbo.CHECKSUM(@idcard)
    if(@sum <> substring(@idcard, 18, 1))
    BEGIN
        --print '第18位校验和验证不符'
        return 0
    END
    --验证行政区号
    if(convert(int,SUBSTRING(@idcard, 1, 2)) > 65 or convert(int,SUBSTRING(@idcard, 1, 2)) < 11)
    BEGIN
        --print '验证行政区不符'
        return 0
    END
    declare @verifydt int
    set @verifydt = 0
    select @verifydt = dbo.checkdate(SUBSTRING(@idcard, 7, 8));
    if(@verifydt = 0)
    BEGIN
        --print '验证日期不符'
        return 0
    END
    declare @nLastThree int;
    set @nLastThree = convert(int, SUBSTRING(@idcard, 15, 3));
    if(@sex = 0 and (@nLastThree % 2 = 0))
    BEGIN
        return 0
    END
    else if(@sex = 1 and (@nLastThree % 2 = 1))
    BEGIN
        return 0
    END
    ELSE
    BEGIN
        return 1
    END
    return 0
END