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

may be u want to check this link

http://omnibuzz-sql.blogspot.com/2006/06/generate-unique-user-ids.html

Ominibuzz,
I did not have problem coming up to a logic to create username - i
think i have good logic on that sense.

I just need help getting compare part working in SQL. It is like read
value from a crusor and campare with select statement value. How do i
do that?

sa


Omnibuzz wrote:
Show quoteHide quote

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.

Got it. Thanks

--CELKO-- wrote:
Show quoteHide quote

SQL question
Passing a parameter as list in a Stored procedure
Incorrect information from a "View"
Function results
selecting on a null col.