Prev: Combining TOP with GROUP BY?
Next: Plz helpppp
From: scott on 21 Jul 2010 15:57
I've got a simple sproc that should add an email address if @iType = 1 or
delete an email address if iType = 0. I can't get it work.
Since I'm using a string variable @recipEmail in the exists tests, do I
need to add quotes around it? I'm not errors, it's just not adding/deleting
the email address.

I think my IF syntax is correct.


CODE:

CREATE PROCEDURE newsSubscribe

(
@recipEmail varchar(75),
@iType int
)
AS
BEGIN

IF @iType = 0 /* delete recip */

IF EXISTS (
SELECT recipEmail
FROM news
WHERE recipEmail = @recipEmail
)
BEGIN
DELETE FROM news WHERE recipEmail=(a)recipEmail
END

ELSE IF @iType = 1 /* add recip */
BEGIN
IF NOT EXISTS (
SELECT recipEmail
FROM news
WHERE recipEmail = @recipEmail
)
BEGIN
INSERT INTO news
(recipEmail, recipDateModified)
VALUES
(@recipEmail, GETDATE())

END
END
END


From: Eric Isaacs on 21 Jul 2010 17:23

Scott,

You didn't describe your problem exactly, but I think the problem
actually may be related to your IF statement. You have two IF
statements followed by one ELSE IF statement. You're assuming that
the ELSE applies to the first IF, but I think it's being applied to
the second IF.

When I format your SQL with Red-Gate's SQL Refactor, it agrees with
what I assumed. Your ELSE is being applied to the inner IF, not the
outer IF.

Here's your reformatted SQL. See where the ELSE lines up with the
inner IF...

CREATE PROCEDURE newsSubscribe
(
@recipEmail varchar(75),
@iType int
)
AS
BEGIN
IF @iType = 0 /* delete recip */
IF EXISTS ( SELECT
recipEmail
FROM
news
WHERE
recipEmail = @recipEmail )
BEGIN
DELETE FROM
news
WHERE
recipEmail = @recipEmail
END

ELSE
IF @iType = 1 /* add recip */
BEGIN
IF NOT EXISTS ( SELECT
recipEmail
FROM
news
WHERE
recipEmail =
@recipEmail )
BEGIN
INSERT INTO
news
(
recipEmail,
recipDateModified
)
VALUES
(@recipEmail, GETDATE())

END
END
END


Here's what you should try...


IF @iType = 0 /* delete recip */
BEGIN
DELETE FROM
news
WHERE
recipEmail = @recipEmail
END

ELSE
IF @iType = 1 /* add recip */
BEGIN
IF NOT EXISTS ( SELECT
recipEmail
FROM
news
WHERE
recipEmail = @recipEmail )
BEGIN
INSERT INTO
news
(
recipEmail,
recipDateModified

)
VALUES
(@recipEmail, GETDATE())

END
END


-Eric Isaacs
 | 
Pages: 1
Prev: Combining TOP with GROUP BY?
Next: Plz helpppp