Help with SP to check and add new user to database

Hi - I'm new to SPs, and wondered if anyone could help with this SP.
It takes a username, email and password - and should register (add) the
details to the customer table. But first, I would like it to check if
the email address already exists in the DB - by performing the first
SELECT statement.
If it does exist, I would like it to just return 0 (and my code in the
.net page will interpret 0 as 'email already exists' and flag that to
the user.
Trouble is, it just keeps returning 0, and not adding the new details.
Thanks for any help,
Mark
CREATE Procedure CustomerAdd
(
@.FullName varchar(50),
@.Email varchar(50),
@.Password varchar(50),
@.CustomerID int OUTPUT
)
AS
SELECT CustomerID FROM Customers WHERE EmailAddress = @.Email
IF @.@.Rowcount >0
SELECT
@.CustomerID = 0
ELSE
INSERT INTO Customers
(
FullName,
EMailAddress,
Password
)
VALUES
(
@.FullName,
@.Email,
@.Password
)
SELECT
@.CustomerID = @.@.Identity
GO
*** Sent via Developersdex http://www.examnotes.net ***
Don't just participate in USENET...get rewarded for it!Try,
CREATE Procedure CustomerAdd
(
@.FullName varchar(50),
@.Email varchar(50),
@.Password varchar(50),
@.CustomerID int OUTPUT
)
AS
set nocount on
declare @.error int
if exists(SELECT * FROM Customers WHERE EmailAddress = @.Email)
set @.CustomerID = 0
else
begin
INSERT INTO Customers
(
FullName,
EMailAddress,
Password
)
VALUES
(
@.FullName,
@.Email,
@.Password
)
set @.error = @.@.error
if @.error = 0
set @.CustomerID = scope_Identity()
else
return -1
end
return 0
GO
AMB
"Mark" wrote:

> Hi - I'm new to SPs, and wondered if anyone could help with this SP.
> It takes a username, email and password - and should register (add) the
> details to the customer table. But first, I would like it to check if
> the email address already exists in the DB - by performing the first
> SELECT statement.
> If it does exist, I would like it to just return 0 (and my code in the
> .net page will interpret 0 as 'email already exists' and flag that to
> the user.
> Trouble is, it just keeps returning 0, and not adding the new details.
> Thanks for any help,
> Mark
>
> CREATE Procedure CustomerAdd
> (
> @.FullName varchar(50),
> @.Email varchar(50),
> @.Password varchar(50),
> @.CustomerID int OUTPUT
> )
> AS
> SELECT CustomerID FROM Customers WHERE EmailAddress = @.Email
> IF @.@.Rowcount >0
> SELECT
> @.CustomerID = 0
> ELSE
> INSERT INTO Customers
> (
> FullName,
> EMailAddress,
> Password
> )
> VALUES
> (
> @.FullName,
> @.Email,
> @.Password
> )
> SELECT
> @.CustomerID = @.@.Identity
> GO
>
>
> *** Sent via Developersdex http://www.examnotes.net ***
> Don't just participate in USENET...get rewarded for it!
>|||Something like this?
CREATE Procedure CustomerAdd
(
@.FullName varchar(50),
@.Email varchar(50),
@.Password varchar(50),
@.CustomerID int OUTPUT
)
AS
IF NOT EXISTS (SELECT * FROM Customers WHERE EmailAddress = @.Email)
BEGIN
INSERT INTO Customers
(
FullName,
EMailAddress,
Password
)
VALUES
(
@.FullName,
@.Email,
@.Password
)
SELECT @.CustomerID = @.@.Identity
RETURN (0)
END
SET @.customerID = 0
/*
you could also do something like this:
SELECT @.CustomerID = CustomerID FROM Customers WHERE EmailAddress = @.Email
*/
RETURN (0)
GO
Keith
"Mark" <anonymous@.devdex.com> wrote in message
news:eg1sIT8AFHA.1084@.tk2msftngp13.phx.gbl...
> Hi - I'm new to SPs, and wondered if anyone could help with this SP.
> It takes a username, email and password - and should register (add) the
> details to the customer table. But first, I would like it to check if
> the email address already exists in the DB - by performing the first
> SELECT statement.
> If it does exist, I would like it to just return 0 (and my code in the
> net page will interpret 0 as 'email already exists' and flag that to
> the user.
> Trouble is, it just keeps returning 0, and not adding the new details.
> Thanks for any help,
> Mark
>
> CREATE Procedure CustomerAdd
> (
> @.FullName varchar(50),
> @.Email varchar(50),
> @.Password varchar(50),
> @.CustomerID int OUTPUT
> )
> AS
> SELECT CustomerID FROM Customers WHERE EmailAddress = @.Email
> IF @.@.Rowcount >0
> SELECT
> @.CustomerID = 0
> ELSE
> INSERT INTO Customers
> (
> FullName,
> EMailAddress,
> Password
> )
> VALUES
> (
> @.FullName,
> @.Email,
> @.Password
> )
> SELECT
> @.CustomerID = @.@.Identity
> GO
>
>
> *** Sent via Developersdex http://www.examnotes.net ***
> Don't just participate in USENET...get rewarded for it!|||On Wed, 26 Jan 2005 08:33:34 -0800, Mark wrote:

>Hi - I'm new to SPs, and wondered if anyone could help with this SP.
>It takes a username, email and password - and should register (add) the
>details to the customer table. But first, I would like it to check if
>the email address already exists in the DB - by performing the first
>SELECT statement.
(snip)
Hi Mark,
No need to do the check in a seperate operation:
CREATE Procedure CustomerAdd
(
@.FullName varchar(50),
@.Email varchar(50),
@.Password varchar(50),
@.CustomerID int OUTPUT
)
AS
INSERT INTO Customers
(
FullName,
EMailAddress,
Password
)
SELECT
@.FullName,
@.Email,
@.Password
WHERE NOT EXISTS
(SELECT *
FROM Customers
WHERE EmailAddress = @.Email)
IF @.@.Rowcount >0
SET @.CustomerID = 0
ELSE
SET @.CustomerID = SCOPE_IDENTITY
(untested)
Another alternative is to create a UNIQUE constraint on the EmailAddress
column and check for errors after the attempted insert (though I
personally prefer to prevent constraint violations).
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)