sql 根据身份证获得省,市,区,出生年月,性别

推荐资料自学网址:http://www.51zxw.net/study.asp?vip=10241777 

create  proc  CheckID (
@IDcardNO varchar(50)    --@IDcardNO 身份证号
)
as
declare @length  int             -- 身份证号位数
declare @r varchar(2)            --判断第18位校验位是否正确
declare @i int                   --用于计算第18位校验位是否正确
declare @sex varchar(2)          --性别
declare @Province  varchar(50)   --省
declare @city  varchar(50)       --市
declare @area   varchar(50)      --区
declare @birthday   varchar(50)  --出生年月

set @length=len(@IDCardNo)
IF @IDCardNo IS NULL OR @IDCardNo = NULL OR LTRIM(RTRIM(@IDCardNo)) = ''
BEGIN
PRINT '身份证号不可为空!'
RETURN
END
--判断位数
if @length<>18  
begin
print '身份证号码位数有误,请核查！'
return
end
IF @length = 18
begin
-- IF ISNUMERIC(LEFT(@IDCardNo, 17)) = 0     --判断前17位是否为数字类型
--  BEGIN 
--   print '身份证号码前17位含特殊符号,请核查！' 
--    RETURN
--     END
-- IF ISDATE(SUBSTRING(@IDCardNo, 7, 4) + '-' + SUBSTRING(@IDCardNo, 11, 2) + '-' + SUBSTRING(@IDCardNo, 13, 2)) = 0
--  begin
--   print '身份证出生年月日不合理,请核查!'   --判断7-14位 出生年月日 是否合理
--    return
--     end
--其中跟据国家规定的计算公式，计算18位身份证检验位 and 验证身份证是否错误
set @i = cast(substring(@IDcardNO,1,1) as int) * 7   
 +cast(substring(@IDcardNO,2,1) as int) * 9  
 +cast(substring(@IDcardNO,3,1) as int) * 10
 +cast(substring(@IDcardNO,4,1) as int) * 5
 +cast(substring(@IDcardNO,5,1) as int) * 8
 +cast(substring(@IDcardNO,6,1) as int) * 4
 +cast(substring(@IDcardNO,7,1) as int) * 2
 +cast(substring(@IDcardNO,8,1) as int) * 1   
 +cast(substring(@IDcardNO,9,1) as int) * 6
 +cast(substring(@IDcardNO,10,1) as int) * 3
 +cast(substring(@IDcardNO,11,1) as int) * 7
 +cast(substring(@IDcardNO,12,1) as int) * 9  
 +cast(substring(@IDcardNO,13,1) as int) * 10 
 +cast(substring(@IDcardNO,14,1) as int) * 5
 +cast(substring(@IDcardNO,15,1) as int) * 8
 +cast(substring(@IDcardNO,16,1) as int) * 4
 +cast(substring(@IDcardNO,17,1) as int) * 2
set @i = @i - @i/11 * 11
set @r = cast((case @i
 when 0 then 1
 when 1 then 0      
 when 2 then 11     
 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) as char)  
if  (@r = 11) 
begin
set  @r='X'
end
else
begin 
set @r = @r
end 
 if substring(@IDcardNO,18,1) <>@r
 begin
 print '身份证输入错误!请核查'
 RETURN
 end
--***********************************
--出生年月
select @birthday=( SUBSTRING(@IDCardNo, 7, 4) + '-' + SUBSTRING(@IDCardNo, 11, 2) + '-' + SUBSTRING(@IDCardNo, 13, 2))
---判断性别
SELECT  @sex=CASE  substring(@IDcardNO,17,1)%2   WHEN   1   THEN   '男'   ELSE   '女'   END  
 --省,市,区
select  @province=province  from sarea$ where  provinceID=left(@IDcardNO,2)  
 
select  @city=city from sarea$ where  cityID=left(@IDcardNO,4)  
 
select    @area=area from sarea$ where  areaID=left(@IDcardNO,6)  
 
select @province as 省,@city as 市 , @area as 地区,@sex as 性别,@birthday  as 出生日期
end