Question for a pro.

[plm]

I am not a pro! Therefore I need some expertise on the following matter:

I have a registration page that is displayed dynamically, if the user is new he registers, but before inserting the new credentials the stored procedure checks if the user name already exists. If yes, then the user is required to chose another user name.

Now, if an existing user wants to change his user name and password, he uses the same page for the update, then I need a new stored procedure to check if the new user name he choses doesn't already exists, because if I use the same sp, the current user will always be denied the update since the user obviously exists as it is itsefl.

Any idea how to build this new procedure or any sample code to refer to.

Thanks.

[skhanal]

Write a IF condition in your stored procedure


IF exists (select * from users where username = @username)

-- login, change password

ELSE

-- create a new user

[plm]

Can you show me how to create this kind of stored procedure?
Thnaks.

Plm

[tonywilko]

You've got two separate issues...
1. Am I doing an update or an insert?
2. I need to ensure uniqueness of usernames.

You'll need to pass in more than the username for existing users. You'll need, as a minimum, the old username and the new username because you'll need to know which record to update if they're changing their username. If you make the old_username parameter optional, you can use this to identify if it's a new user or an existing user. New users won't have an old username!

Now, just ensure uniqueness for the new_username parameter... this applies to everybody... old and new.

eg.

if exists( select username
from tblUser
where username = new_username)
begin
--get out of here! it doesn't matter if you're a new user or an existing user... this username is taken!
raiserror('Username problem!',16,1)
return
end

if old_username is not null
begin
--existing user doing an edit
--do update where the username = old_username
end
else
begin
-- new registration
-- do insert
end

[plm]

Tony,

Thanks for guiding me. I thought this over couple of times and I believe that I just need to create a @newUsername variable in my registration.aspx page without changing my user table.

If you are using SQL Server open Query Annalyser and paste the following code code and let me know what do you think.

It seems to be working now I have to try it out in my ASPX page. If you think that I am doing wrong let me know.

slts.
plm

DECLARE @UserName NVARCHAR (10)
SET @UserName = 'kim'

IF EXISTS(SELECT UserName FROM Person WHERE UserName = @UserName)
BEGIN
RAISERROR ('User name already exists',16,1)
RETURN
END

DECLARE @newUserName NVARCHAR (10)
SET @newUserName = @UserName

IF @newUserName is not null
BEGIN
UPDATE Person
SET UserName = @newUserName
Where PersonID = 1
RETURN
END

[tonywilko]

plm,

OK, my understanding of the situation is that you need a single stored procedure to maintain the Person table. To insert new records for new registrations and also to update values for existing registrations. Is this correct?

If so, there are a couple of things about the code you posted.

1. The @newUserName variable looks a little redundant... you are just setting it to be the value of the @UserName variable (which I'm guessing is an input parameter to the proc), in which case you should just simply use the @UserName variable.

2. In your code a PersonID of 1 has mysteriously appeared... if you refer to my previous reply you'll see that I mention that you'll need an identifier for the record to update... In your code you have done this by updating where the PersonID = 1. If you're using the PersonID field to identify the record you will of course need to make this value an input parameter to the proc.

Look at an example code below... (you may need to make adjustments according to your table structure)...

CREATE PROC SP_MAINTAIN_PERSON
@NewUsername varchar(50),
@Password varchar(50),
@PersonID integer = null
AS

/**************************************/
--does the username already exist
/**************************************/
if exists( select PersonID
from Person
where Username = @username
and (@PersonID != PersonID or @PersonID is null)
begin
raiserror('Username not unique',16,1)
return 0
end

/**************************************/
--is this an update for an existing user or an insert for a new user... (use the PersonID as your clue)
/**************************************/
if not @PersonID is null
begin
update Person
set Username = @Username,
Password = @Password
where PersonID = @PersonID
end
else
begin
insert username(Username,Password)
values (@Username, @Password)
end

/*************************************/
--return the id of the person record (i'm assuming you have an incremental integer counter for the person id field)
/*************************************/
return @@identity


Regards

Tony

[plm]

Tony,

Absolutely, I want to use a unique sp to maintain existing users and create new ones without duplicating the username.

I have indeed a unique identifier in my table which is the PersonID, an INT with an increment of 1.

There is something that puzzle me in your code, as I can't get it run:

if exists( select PersonID
from Person
where Username = @username
and (@PersonID != PersonID or @PersonID is null)
begin
raiserror('Username not unique',16,1)
return 0
end

What is the meanning of this part of the code :
(@PersonID != PersonID or @PersonID is null)

I don't understand it.

The second part of the code is straight forward and I understand it.

rgds.

plm

[tonywilko]

plm,

The line of code relates to the fact that for new registrations you won't have a PersonID to send in as a record identifier.

Think about the different scenarios this code has to deal with...

1. Existing user updating password only - in this instance the username you pass in will be the users existing username so you don't want this to be an error condition... (the user is keeping the same username).. so the code:
where Username = @username
and (@PersonID != PersonID or @PersonID is null)

translates as... where the username equals the Username parameter AND it's not me! (...ignore the or @PersonID is null for now...)

2. New user registration - so @PersonID will be null... the 'or PersonID is null' allows the username check to be performed on all person records so a new user can't choose an existing username...

3. Existing user changing username - in this instance the username check will check all user records except the users own record and fail if a duplicate username is found.

Just reading that back to myself I've realised I probably could have explained a bit better... there's quite a bit going on...

To summarise... you need to check for duplicate usernames with the single exception of a user updating their own record... password for instance or any other values you store in the Person table... this is why we exclude the user's own person record from the check... if the @personID is null, in the case of the new registration, this exclusion will be ignored in favour of the "or @PersonID is null"...

Hope this helps!

Regards

Tony