--create type NumbersTable as table(

--Id int,

--Number int

--)



--create view GetRand

--as

--select RAND() as Value



create function GetIdNumber()

returns char(11)

as

begin



declare @toplam1 int, @toplam2 int, @i int, @kimlikNo char(11), @temp int, @rand float

declare @Numbers NumbersTable



select @rand = Value from GetRand

set @temp = convert(int,  @rand * 9 + 1)

insert into @Numbers values(1, @temp)



set @i = 2

while @i < 10

begin

select @rand = Value from GetRand

set @temp = convert(int,  @rand * 10)

insert into @Numbers values(@i, @temp)



set @i = @i +1

end



select @toplam1 = SUM(Number) * 7 from @Numbers where Id in (1,3,5,7,9)

select @toplam2= SUM(Number) from @Numbers where Id in (2,4,6,8)

set @temp = (@toplam1 - @toplam2) % 10

insert into @Numbers values(10, @temp)



select @temp = SUM(Number) % 10  from @Numbers 

insert into @Numbers values(11, @temp)



select @kimlikNo = SUBSTRING((select '' + CAST(Number as varchar) from @Numbers for xml path('')), 0 ,2000)

return @kimlikNo

end