Need Help With User Creation Stored Procedure

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Need Help With User Creation Stored Procedure

Author

Topic

writhe
Starting Member

15 Posts

Posted - 2002-06-07 : 19:19:51

I apologize because I'm a bit of a newbie. Here goes:

I'm trying to write a stored procedure that will create a record for a new user if the user name and email address do not already exist in the user table. I'd like to return different values for each possibility: email address has been used, user name has been used, or creation of record was a success.

This is my code so far:

CREATE PROCEDURE dbo.sp_CreateUser
@FirstName varchar(20),
@LastName varchar(20),
@Email varchar(50),
@UserName varchar(20),
@UserPassword varchar(20)
AS

IF EXISTS(SELECT UserName FROM users
WHERE UserName = @UserName)
RETURN(1)
ELSE IF EXISTS(SELECT UserPassword FROM users
WHERE UserPassword = @UserPassword)
RETURN(2)
ELSE
RETURN(0)

GO

The last ELSE statement is where I'll put the code to actually create a new record. I've used ELSE IF here, but I'm not sure if that is syntactically correct. If not, how can I do this?

kannanonline
Starting Member

1 Post

Posted - 2002-06-08 : 07:51:20

alter procedure dbo.sp_CreateUser(
@FirstName varchar(20),
@LastName varchar(20),
@Email varchar(50),
@UserName varchar(20),
@UserPassword varchar(20),
@returnval int output)
AS
declare @usercount varchar(20),@emailcount varchar(20)
set nocount on
select @usercount=username from users where username=@username
select @emailcount=email from users where email=@Email
if (@usercount=@username)
begin
return (2)
end
else if (@emailcount=@email)
begin
return (1)
end
else
begin
insert into users values(@Username,@Email)
end

writhe
Starting Member

15 Posts

Posted - 2002-06-12 : 18:20:24

Thanks, that did the trick.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources