September 29, 2005 at 8:15 am
Hello,
In SQL Server 2000 we have a User table with Login IDs and other user authentication related data for an application. Employee A has a login ID of A1. Employee A leaves the company. Employee B joins the company and needs to use this particular application. We wan to recycle the login ID A1 from Employee A and assign it to the new Employee B, but we cannot simply delete the Employee A reference from the user table for historical data reporting purpose. We need to rename the original A1 login ID to something like A1O the first time it's recycled then A1O1 the second time, so on and so forth. So the first time employee login ID is being recycled it will always be added O at the end of the ID (to the right), but each subsequent time it's being recycled it will get a number added in addition to O like O1, O2, O3, etc.
I need to do this programmatically in a SPROC and I am having difficulty coming up with a most logical solution, especially since login ID is a VARCHAR field so I cannot just do ID + 1. The SPROC will be supplied one parameter of the original ID being recycled then using that I need to find if it's been recycled already and what's the last recycled ID value? Then add an applicable letter O or O1 likes to the right end of the ID, incrementing the number part by 1, as necessary keeping in mind the ID field is of VARCHAR datatype.
Anyone can suggest a logical solution? I got up to the verification process and now find myself stuck on the part where I need to find the last ID recycled (if recycled) and then increment the new one with 1 from the last ID recycled value. If you can through some light on this or offer an entirely different way of doing this, which I have not been able to think of then I'd appreciate it.
Thanks.
JN
September 29, 2005 at 9:22 am
I don't know exactly what you are looking for. If you just want a logic for incrementing the userid, try this
declare @userid varchar(100), @delimpos int, @delimiter char(1)
set @userid ='A1'
set @delimiter = 'O'
set @delimpos = charindex(@delimiter ,@userid)
if @delimpos = 0
set @userid = @userid + @delimiter
else
set @userid = left( @userid,@delimpos) + convert(varchar,(substring( @userid,@delimpos+1,len( @userid))) +1)
select @userid
I would suggest using a different delimiter other than 'O' if your userd id has embedded delimiters this code will not work correctly
HTH
September 29, 2005 at 10:22 am
Hi try this
drop table #Usrs
create table #Usrs (LoginId varchar(10), Name varchar(40))
declare @Uid varchar(10), @Cntr int
insert into #Usrs values ('AA','EEEE')
select @Uid = LoginId from #Usrs
select substring (@Uid,len(@Uid), 1)
if substring (@Uid,len(@Uid), 1) <>'O'
begin
if isnumeric(substring (@Uid,len(@Uid), 1)) <>1
begin
select @Uid = @Uid +'O'
end
else
if isnumeric(substring (@Uid,len(@Uid), 1)) =1
begin
select @Cntr = convert(int,substring (@Uid,len(@Uid), 1)) +1
select @Uid = substring(@Uid, 1, len(@uid)-1) +convert(varchar,@Cntr)
end
end
else
begin
select @Uid = @Uid +'1'
end
select @Uid
update #Usrs set LoginId = @Uid
Helen
--------------------------------
Are you a born again. He is Jehova Jirah unto me
September 29, 2005 at 10:39 am
Following is my solution and logic, in case anyone interested:
CREATE Procedure dbo.usp_Recycle_E_Login_ID
/*
*********************************************
Name: dbo.csp_UPD_Employee_Login_ID
Purpose: Update an existing Login Name in Table_User so that it can be recycled to set up a new user and save the original ID/User for historical purpose.
Author: JN
Created: 9/27/2005
Tested: 9/27/2005, 1:20PM
Test ID: A01234
*********************************************
-*-*-*- REVISIONS -*-*-*-
9/30/2005 JN: Modified to remove @New_Login_ID param from being supplied a value by end-user executing this SPROC...
---Cont.------ The SPROC programatically assigns a new value to Login Name being recycle based on existing Login Name...
---Cont.------ and/or the last recycled value of that Login Name.
*********************************************
*/
@Login_ID VARCHAR(30)
AS
SET NOCOUNT ON
DECLARE @Rows INT
-- Verifies that the Login Name being updated does exist in the Table_User table.
SELECT @Rows = COUNT(*)
FROM dbo.table_e E
INNER JOIN dbo.table_user U ON E.e2user = U.oid
WHERE (((U.Login_ID) Like @Login_ID))
IF @Rows = 0
BEGIN
PRINT @Login_ID + ' does not exist!'
RETURN
END
ELSE
-- Verifies the status of the Login Name being updated is 0 meaning INACTIVE.
SET @Rows = 0
SELECT @Rows = COUNT(*)
FROM dbo.table_e E
INNER JOIN dbo.table_user U
ON E.e2user = U.oid
WHERE (((U.Login_ID) Like @Login_ID + '%') AND ((U.Status)<>1))
IF @Rows = 0
BEGIN
PRINT @Login_ID + ' is an ACTIVE Login with status code 1. An active login cannot be recycled!'
RETURN
END
ELSE IF @Rows = 1
BEGIN
-- If all verification steps above raised NO issues then proceed with Login Name UPDATE in Table_User table.
BEGIN TRAN
UPDATE dbo.table_user
SET Login_ID =
CASE
WHEN RIGHT(Login_ID, 1) = 'O' THEN Login_ID + '1'
WHEN RIGHT(Login_ID, 2) = 'O1' THEN Login_ID + CAST(RIGHT(Login_ID, 1) + 1 AS VARCHAR(2))
WHEN Login_ID = @Login_ID THEN Login_ID + 'O'
END
WHERE ((Login_ID Like @Login_ID) AND (Status <> 1))
IF @@Error <> 0
BEGIN
ROLLBACK TRAN
PRINT 'User_Table update for login name ' + @Login_ID + ' was unsuccessfull.'
END
ELSE
BEGIN
COMMIT TRAN
PRINT 'Login name ' + @Login_ID + ' was successfully updated to a new login name.'
END
END
GO
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply