SQLServer2005 身份证函数,含验证和15位转18位

Author:水如烟

SQLServer2005 身份证函数,含验证和15位转18位

USE   [ LzmTWWorks ]
GO
/* ***** 对象:  UserDefinedFunction [Helper].[IDCard]    脚本日期: 12/07/2007 23:21:37 ***** */
SET  ANSI_NULLS  ON
GO
SET  QUOTED_IDENTIFIER  ON
GO
CREATE   FUNCTION   [ Helper ] . [ IDCard ]  
(
     @Card      varchar ( 18 )
)
RETURNS  
@TCard   TABLE  
(
     Input     varchar ( 18 )
    ,IDCard     varchar ( 18 )
    ,Valid     bit
)
AS
BEGIN
     DECLARE     
              @Input          as   varchar ( 18 )
            , @IDCard      as   varchar ( 18 )
            , @Valid          as   bit

     DECLARE      
              @Length      as   smallint
            , @TmpCard      as   varchar ( 18 )
            , @IsOld          as   bit

     SET   @Valid   =   0
     SET   @IDCard   =   ''
     SET   @Input   =   ''

     IF   @Card   IS   NULL   GOTO  Finish

     SET   @Input   =   LTRIM ( RTRIM ( @Card ))  /* 去空格 */
     SET   @Length   =   LEN ( @Input )

     IF   NOT   @Length   IN  ( 15 ,  18 )  GOTO  Finish  /* 非15、18位 */

     IF   @Length   =   15
         BEGIN
             IF   ISNUMERIC ( @Input )  =   0   GOTO  Finish  /* 非数字 */
             SET   @TmpCard   =   LEFT ( @Input ,  6 )  +   ' 19 '   +   RIGHT ( @input ,  9 )  /* 补充为17位 */
             SET   @IsOld   =   1
         END
     ELSE
         BEGIN
             IF   ISNUMERIC ( LEFT ( @Input ,  17 ))  =   0   GOTO  Finish  /* 非数字 */
             SET   @TmpCard   =   LEFT ( @Input ,  17 )  /* 取前17位 */
             SET   @IsOld   =   0
         END

     DECLARE   @Birthday      varchar ( 8 )
     SET   @Birthday   =   SUBSTRING ( @TmpCard ,  7 ,  8 )
     IF   ISDATE ( @birthday )  =   0   GOTO  Finish  /* 非日期 */

     -- 前17位数与相应加权因子的积的和
     DECLARE  
              @Sum   as   smallint
            , @WI   as   tinyint
            , @Index   as   tinyint
            , @Num   as   tinyint

     SET   @Sum   =   0
     SET   @Index   =   1