Help with this SQL script.

SQL Apprentice Question
I have code below to analyze existing network username to potential new
user's usernames. If no match then user gets first initial and lastname if
there is a match (conflict) then if the person have middle name then first
initial, first character of middle and last name else gets first two
character from their firstname and lastname and so on.

I have following code (bottom) working in Access., but would like to covert
this into MS SQL 2000 PROC. Here is what i have so far and it is not working.


Please help


/* Drop temporary UserNames table if it already exists */
if exists(select * from sysobjects where name = 'temp_user_names')
drop table temp_user_names


/* Drop temporary Current AD Users table if it already exists */
if exists(select * from sysobjects where name = 'temp_current_AD_names')
drop table temp_current_AD_names


/* Re-create temporary user names table */
create table temp_user_names (
id_num int null,
UserName char(45) null,
First_name char(45) null,
Last_name char(45) null,
Middle_name char(45) null)


/* Re-create temporary current AD Uusers Table */
create table temp_current_AD_names (UserName char(45) null, Id_num int)


/*insert current list of active directory user names to temp table */
insert into temp_current_AD_names (UserName, Id_num)
select SAMAccountName, EmployeeID from ViewOCADUsers


/* create a crusor and insert new students list into it. */
DECLARE NewStudent cursor for
select id_num, first_name, Last_name, Middle_name from ViewUserNeedUserName


open NewStudent


DECLARE @new_id_num int,
@new_first_name Char(45),
@new_last_name Char(45),
@new_middle_name Char(45),
@UserName Char (45),
@lngStep int,
@mnCheck int


FETCH NEXT FROM NewStudent
INTO @new_id_num, @new_first_name, @new_last_name, @new_middle_name


while (@@fetch_status <> -1)
begin


set @lngStep = 1
set @mnCheck = 0
set @UserName = LEFT(@new_first_name, @lngStep) + @new_last_name


While @UserName = Select RTRIM(UserName) from ViewOCADUsers where @UserName
begin


if not isnull(@new_middle_name) and (@mnCheck = 0) the
set @UserName = LEFT(@new_first_name, @lngStep) + LEFT(@new_middle_name,
1) + @new_last_name
set @mnCheck = 1


else
set @lngstep = @lngstep +1
set @UserName = LEFT(@new_first_name, @lngStep) + @new_last_name
end


INSERT into temp_user_names VALUES (@new_id_num, @new_last_name,
@new_First_name, @new_Middle_name, @UserName)


FETCH NEXT FROM NewStudent
INTO @new_id_num, @new_first_name, @new_last_name, @new_middle_name
end


CLOSE NewStudent
DEALLOCATE NewStudent


Access 2000 Code:


Private Sub Command0_Click()
Dim StrSql2 As String
Dim strSqlUpdate As String
Dim rsn As DAO.Recordset
Dim TempUserName As String
Dim lngStep As Integer


StrSql2 = "SELECT id_num, last_name, first_name, middle_name FROM
Admission;"


Set rsn = CurrentDb.OpenRecordset(StrSql2)


rsn.MoveFirst


Do While Not rsn.EOF


lngStep = 1
Dim mnCheck As Boolean
mnCheck = False
'Propose the first username
TempUserName = Left(rsn!First_name, lngStep) + rsn!Last_Name


'Check to see if the user name exists
Do While Not IsNull(DLookup("UserName", "qryUserName", "UserName =
'" & TempUserName & "'"))


'If so, try the next one


If Not IsNull(rsn!middle_name) And mnCheck = False Then
TempUserName = Left(rsn!First_name, 1) +
Left(rsn!middle_name, 1) + rsn!Last_Name
mnCheck = True
Else
lngStep = lngStep + 1
TempUserName = Left(rsn!First_name, lngStep) + rsn!Last_Name
End If
Loop


strSqlUpdate = "INSERT into temp_user (id_num, last_name,
First_name, Middle_name, UserName) VALUES ( " & rsn!ID_num & ", '" &
rsn!Last_Name & " ', '" & rsn!First_name & "', '" & rsn!middle_name & "' , '"
& TempUserName & "');"
CurrentDb.Execute strSqlUpdate


rsn.MoveNext


Loop


Set rsn = Nothing


DoCmd.OpenTable "temp_user"
End Sub



Celko Answers
>>I have code below to analyze existing network username to potential new
user's usernames. If no match then user gets first initial and lastname
if
there is a match (conflict) then if the person have middle name then
first
initial, first character of middle and last name else gets first two
character from their firstname and lastname and so on.


I see that you hate non-procedural programming, like weak security, do
not know what a key is and that you know a lot of people with very long
names --CHAR(45)? Wow, can I see the research on that?


Try something more like this:


CREATE TABLE Users
(user_name VARCHAR(25) NOT NULL PRIMARY KEY,
first_name VARCHAR(25) NOT NULL
CHECK(first_name = LTRIM(RTRIM(first_name)),
middle_name VARCHAR(25) NOT NULL
CHECK(middle_name = LTRIM(RTRIM(middle_name)),
last_name VARCHAR(25) NOT NULL
CHECK(last_name = LTRIM(RTRIM(last_name))
);


Then use an insert statement will all your rules in it.


INSERT INTO Users (
VALUES (CASE WHEN
NOT EXISTS U.user_name
= SUBSTRING (@first_name, 1, 1) + @last_name
THEN SUBSTRING (@first_name, 1, 1) + @last_name
WHEN
NOT EXISTS U.user_name
= SUBSTRING (@first_name, 1, 1)
+ SUBSTRING (@middle_name, 1, 1)
+ @last_name
THEN SUBSTRING (@first_name, 1, 1)
+ SUBSTRING (@middle_name, 1, 1)
+ @last_name


etc.


ELSE NULL END, -- will cause error
@first_name, @middle_name, @last_name);


One statement, pure SQL and the trimming is done in the DDL where it
belongs.