IF  EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[dbo].[TcKimlikNoGecerli]') AND xtype in (N'FN', N'IF', N'TF'))
DROP FUNCTION [dbo].TcKimlikNoGecerli
GO

CREATE FUNCTION [dbo].[TcKimlikNoGecerli](@TcNo Varchar(100))
RETURNS BIT
AS
BEGIN
DECLARE @bigintTCNO Bigint,
	@ATCNO 	Bigint,
	@C1 	Tinyint,
	@C2 	Tinyint,
	@C3 	Tinyint,
	@C4 	Tinyint,
	@C5 	Tinyint,
	@C6 	Tinyint,
	@C7 	Tinyint,
	@C8 	Tinyint,
	@C9 	Tinyint,
	@Q1 	Int,
	@Q2 	Int,
	@R 		Bit

IF Len(@TcNo) <> 11
BEGIN
  SET @R = 0
  RETURN @R
END

if IsNumeric(@TcNo) = 0
BEGIN
  SET @R = 0
  RETURN @R
END

SET @bigintTCNO = CAST(@TcNo as bigint)
IF @bigintTCNO = 0
BEGIN
  SET @R = 0
  RETURN @R
END

SET @ATCNO = @bigintTCNO / 100
 
SET @C1 = CAST(SUBSTRING(@TcNo,9,1) as tinyint)
SET @C2 = CAST(SUBSTRING(@TcNo,8,1) as tinyint)
SET @C3 = CAST(SUBSTRING(@TcNo,7,1) as tinyint)
SET @C4 = CAST(SUBSTRING(@TcNo,6,1) as tinyint)
SET @C5 = CAST(SUBSTRING(@TcNo,5,1) as tinyint)
SET @C6 = CAST(SUBSTRING(@TcNo,4,1) as tinyint)
SET @C7 = CAST(SUBSTRING(@TcNo,3,1) as tinyint)
SET @C8 = CAST(SUBSTRING(@TcNo,2,1) as tinyint)
SET @C9 = CAST(SUBSTRING(@TcNo,1,1) as tinyint)
SET @Q1 = ((10-((((@C1+@C3+@C5+@C7+@C9)*3)+(@C2+@C4+@C6+@C8)) % 10))%10)
SET @Q2 = ((10-(((((@C2+@C4+@C6+@C8)+@Q1)*3)+(@C1+@C3+@C5+@C7+@C9))%10))%10)
 
IF (@ATCNO * 100)+(@Q1 * 10)+@Q2 = @bigintTCNO 
  SET @R = 1 
ELSE 
  SET @R = 0
 
RETURN @R
END
GO

IF EXISTS (SELECT [name] FROM [sys].[database_principals] WHERE [type] = N'S' AND [name] = N'mediwebuser')
BEGIN
	BEGIN TRY
		GRANT EXECUTE ON [dbo].[TcKimlikNoGecerli] TO [mediwebuser]
	END TRY
	BEGIN CATCH
	END CATCH
END
GO

