Big nested IF statement?

[jasonsalas]

I've got a rather interesting problem...I've got the a single table with EMAIL and MOBILE fields both of type VARCHAR used in an alert registration system. Users can register their e-mail address and/or a mobile number, either at the same time or coming back and registering the other one later.

My problem is that I've got to:
1) Refuse registration if the user's already in the system for either or both devices
2) Allow registration for an additional device if the other one already exists
3) Register new users not already in the system

I was thinking a nested IF...THEN sproc would do the trick, but it's turning out to be a big mess. Here's what I was messing around with:

CREATE PROCEDURE SubscribeUserDevicesToAlerts
(
@Email VARCHAR(65),
@Mobile VARCHAR(25)
)

AS

IF EXISTS(SELECT Email,Mobile FROM Alerts WHERE Email = @Email AND Mobile = @Mobile)
BEGIN
-- invalid entry
RETURN -1
END
ELSE
BEGIN
IF EXISTS(SELECT Email FROM Alerts WHERE Email = @Email)
BEGIN
-- e-mail address already exists, update mobile number
UPDATE Alerts SET Mobile = @Mobile
END

IF EXISTS(SELECT Mobile FROM Alerts WHERE Mobile = @Mobile)
BEGIN
-- update mobile number only
UPDATE Alerts SET Email = @Email
END
END

-- register brand new users
INSERT INTO Alerts Email,Mobile) VALUES @Email,@Mobile)
GO

I've got either volumnuous code or replicating INSERTs, as many as 8 times. Anyone have any other better ideas?

 

[vbkris]

hi,

guess this will work


IF EXISTS(SELECT Email,Mobile FROM Alerts WHERE Email = @Email AND Mobile = @Mobile)
BEGIN
-- invalid entry
RETURN -1
END


IF EXISTS(SELECT Email FROM Alerts WHERE Email = @Email or Mobile = @Mobile)
BEGIN
-- e-mail address already exists, update mobile number
UPDATE Alerts SET Mobile = @Mobile, Email = @Email
return -1
END

-- register brand new users
INSERT INTO Alerts Email,Mobile) VALUES @Email,@Mobile)


assumption:
-> the mobile and email id are always given.
-> they have the values to be updated.


Known is handfull, Unknown is worldfull