--
=============================================
-- Author: Jon Vote
-- Create date: 2015-01-11
-- Description: Create Names
--
=============================================
Create PROCEDURE [dbo].[CreateNames]
@NumRecords int = 1000
AS
BEGIN
SET NOCOUNT ON;
Declare @NumFirstNames
int = (Select Max(Id) from FirstNames);
Declare @FirstNameId int;
Declare @FirstName nvarchar(50);
Declare @Gender char;
Declare @NumLastNames
int = (Select MAX(Id) from LastNames);
Declare @LastNameId int;
Declare @LastName nvarchar(50);
Declare @MiddleInitial
varchar(1);
Declare @DOB Date;
Declare @NumDays int;
Declare @SSN varchar(11);
Declare @Count int = @NumRecords;
while @Count > 0 begin
set @FirstNameId = RAND() * @NumFirstNames + 1;
set @LastNameId = RAND() * @NumLastNames + 1;
Select @FirstName = FirstName,
@Gender = Gender
From FirstNames
Where Id = @FirstNameId;
Select @LastName = LastName
From LastNames
Where Id = @LastNameId;
Declare @Matches int = 1;
While @Matches > 0 begin
Set @SSN = convert(varchar, convert(int, RAND() * 10))
+ convert(varchar, convert(int, RAND() * 10))
+ convert(varchar, convert(int, RAND() * 10))
+ '-'
+ convert(varchar, convert(int, RAND() * 10))
+ convert(varchar, convert(int, RAND() * 10))
+ '-'
+ convert(varchar, convert(int, RAND() * 10))
+ convert(varchar, convert(int, RAND() * 10))
+ convert(varchar, convert(int, RAND() * 10))
+ convert(varchar, convert(int, RAND() * 10));
Set @Matches = (Select Count(*) From Names where SSN = @SSN);
end
Set @NumDays = RAND() * 50 * 365;
Set @DOB = DateAdd(Day, @NumDays, '1950-01-01');
Set @MiddleInitial = Char(RAND() * 26 + ASCII('A'));
Insert Into Names
(
FirstName,
LastName,
MiddleInitial,
Gender,
DOB,
SSN
)
Values (
@FirstName,
@LastName,
@MiddleInitial,
@Gender,
@DOB,
@SSN
);
Set @Count = @Count - 1;
end;
END