ALTER FUNCTION DBO.IDTRUE - Verify that the eighteenth ID card is correct (@ID varchar (18)) returns bitas begin - verify that the format is correct Declare @res bitDeclare @W Table (RN Int, val int) Insert Into @w (RN, VAL) SELECT 1,7UNION SELECT 2, 9UNION SELECT 3, 10UNION SELECT 4,5UNION SELECT 5, 8UNION SELECT 6,4UNION SELECT 7,2UNION SELECT 10, 3UNION SELECT 9,6UNION SELECT 10, 3UNION SELECT 11,7UNION SELECT SELECT 12,9Union Select 13,10Union Select 14,5UNION SELECT 15, 8UNION SELECT 16,4UNION SELECT 17, 2UNION SELECT 18, 1DECLARE @A Table (Rwn Int, Val varchar (1)) Insert Into @A (RWN, VAL) SELECT 0, '1'Insert Into @A (RWN, VAL) SELECT 1,' 0'Insert Into @A (RWN, VAL) SELECT 2, 'X'Insert Into @A (RWN, VAL) SELECT 3,' 9 ' INSERT INTO @A (RWN, VAL) SELECT 4, '8'Insert Into @A (RWN, VAL) SELECT 5,' 7'Insert Into @A (RWN, VAL) SELECT 6, '6'Ssert Into @A ( RWN, VAL) SELECT 7, '5'Insert Into @A (RWN, VAL) SELECT 8,' 4'Insert Into @A (RWN, VAL) SELECT 9, '3'Insert Into @A (RWN, VAL) SELECT 10, '2'Declare @i intDeclare @J INTDECLARE @S INT
If LEN (@ID) = 18 Begin Select @ s = 0, @ i = 1 declare @IDI int declare @wi int declare @pii varchar (1) while @i <= 17 begin select @ PII = Substring (@ID, @ i, 1) if @PII Like '[0-9]' Begin Select @ idi = Convert (int, @ PII) SELECT @ Wi = Val from @w where rn = @ i select @ J = @ iDi * @ wi Select @ s = @ s @ J SELECT @ i = @ i 1 end else begin select @ res = 0 return @res endend select @ s = @ s% 11 select @ idi = communication (int, substring (@ ID, 18 , 1)) Declare @pi varchar (1) SELECT @ Pi = Val from @a where rwn = @ s if @ Pi = @ idi select @ res = 1 else select @ res = 0 end else begin select @ res = 0 endreturn @ResendgodeClare @PC bitslect @ pc = dbo.idtrue ('420324198101031224') Select @pcgo
ALTER FUNCTION SFZ15TO18 - Fifteen Rising Eighteen (@sfz varchar (15)) Returns varchar (18) AS Begin Declare @W Table (RN INT, VAL INT) INSERT INTO @W (RN, VAL) SELECT 1,7Union SELECT 5, 8UNION SELECT 3, 10UNION SELECT 9, 6UNION SELECT 12, 9UNION SELECT 11,10UNION SELECT 12, 9UNION SELECT 13, 10UNION SELECT 14 , 5UNION SELECT 15, 8UNION SELECT 16, 4UNION SELECT 17, 2UNION SELECT 18, 1DECLARE @A Table (rwn int, val varchar (1)) Insert Into @A (rwn, val) SELECT 0, '1'Insert Into @a (RWN, VAL) SELECT 1, '0'Insert Into @A (RWN, VAL) SELECT 2,' X'Insert Into @A (RWN, VAL) SELECT 3, '9'Insert Into @A (RWN, VAL) SELECT 4, '8'Ssert Into @a (rwn, val) SELECT 5,' 7'Insert Into @A (rwn, val) SELECT 6, '6'Ssert Into @A (rwn, val) SELECT 7,' 5 'INSERT INTO @A (RWN, VAL) SELECT 8,' 4'Insert Into @A (RWN, VAL) SELECT 9, '3'Insert Into @A (RWN, VAL) SELECT 10,' 2'Declare @newid varchar (18) Select @ newid = Substring (@ sfz, 1, 6) '19' substring (@ sfz, 7, 9) declare @i intDeclare @J IN TDECLARE @S INTSELECT @ s = 0, @ i = 1Declare @IDI INTDECLARE @wi intDeclare @pii varchar (1) while @i <= 17 begin select @ PII = Substring (@ newid, @ i, 1) if @PII Like '[0-9]' Begin Select @ idi = Convert @ wi = val from @w where rn = @ i select @ J = @ iDi * @ wi select @ s = @ s @ J SELECT @ i = @ i
1 END ELSE BEGIN RETURN '' END END SELECT @ s = @ s% 11 declare @pi varchar (1) SELECT @ Pi = Val from @a where rwn = @ s select @ newid = @ newid @ pi return @newid
ENDGOSELECT DBO.SFZ15TO18 ('420324810103153 ") GO - Date correct Alter Function Chkymd (@Y INT, @m tinyint, @d tinyint, @cy int) Returns Bitasbegin Declare @res bit select @ res = 1 if @Y < 1900 or @Y> @cy begin select @ res = 0 return @res end if @ m = 1 or @ m = 3 or @ m = 5 or @ m = 7 or @ m = 8 or @ m = 10 or @m = 12 Begin if (@d> 31) Begin select @ res = 0 return @res end @ m = 2 Begin IF ((@ Y% 4) = 0) and ((@Y) % 100) <> 0) OR ((@Y% 400) = 0) Begin - Leap Year IF (@d <1) or (@d> 29) Begin Select @ res = 0 return @res end Else Begin IF (@d <1) or (@d> 28) begin select @ res = 0 return @res end end @ m = 4 or @ m = 6 or or @ m = 9 or @ m = 11 b EGIN IF (@d> 30) Begin select @ res = 0 return @res end end return @res endgoselect dbo.chkymd (1981, 1, 3, year (getdate ())) GO / * Y: Year, M: Moon, D: Day. Returns the integer value of the respective arguments are passed: 0 Monday of Week 2 Weeks 4 2 weeks 33 4 weeks 55 66 week Week 7 * /
