CREATE VIEW thisDate --返回当前日期,因为自定义函数中不能使用GETDATE() AS SELECT convert(VARCHAR(8),getdate(),112) AS aDate Create function Check_Sfz(@sfzh char(18)) /*mssql2000 返回值=0,身份证校验正确 1:位数不对 2:含有不规则字符 3:日期不对 4:校验位不对 */ returns tinyint as begin set @sfzh=ltrim(rtrim(@sfzh)) declare @r char(1),@resu TINYINT,@thisDate VARCHAR(8) declare @i INT SELECT @thisDate=aDate FROM thisDate if len(@sfzh) <> 18 set @resu=1 ELSE BEGIN IF isnumeric(substring(@sfzh,1,17))=0 --检查前17位是否为数字 set @resu=2 ELSE IF isDate(substring(@sfzh,7,8))=0 --日期是否符合规则 set @resu=3 ELSE IF (substring(@sfzh,7,4)<'1900') OR (substring(@sfzh,7,8)>=@thisDate) --日期是否符合逻辑 SET @resu=3 else BEGIN --检查第18位数据的正确性 set @i = cast(substring(@sfzh,1,1) as int) * 7 + cast(substring(@sfzh,2,1) as int) * 9 + cast(substring(@sfzh,3,1) as int) * 10 + cast(substring(@sfzh,4,1) as int) * 5 + cast(substring(@sfzh,5,1) as int) * 8 + cast(substring(@sfzh,6,1) as int) * 4 + cast(substring(@sfzh,7,1) as int) * 2 + cast(substring(@sfzh,8,1) as int) * 1 + cast(substring(@sfzh,9,1) as int) * 6 + cast(substring(@sfzh,10,1) as int) * 3 + cast(substring(@sfzh,11,1) as int) * 7 + cast(substring(@sfzh,12,1) as int) * 9 + cast(substring(@sfzh,13,1) as int) * 10 + cast(substring(@sfzh,14,1) as int) * 5 + cast(substring(@sfzh,15,1) as int) * 8 + cast(substring(@sfzh,16,1) as int) * 4 + cast(substring(@sfzh,17,1) as int) * 2 set @i = @i - @i/11 * 11 set @r = (case @i when 0 then '1' when 1 then '0' when 2 then 'x' when 3 then '9' when 4 then '8' when 5 then '7' when 6 then '6' when 7 then '5' when 8 then '4' when 9 then '3' when 10 then '2' else '/' end) IF @r=lower(substring(@sfzh,18,1)) set @resu=0 ELSE SET @resu=4--验证位不正确 end end return(@resu) END