SET a variable with IF ELSE

[Iamthestig]

Can someone please help me with this?

DECLARE @REF int
DECLARE @PreferredEmail as varchar(50)
SET @REF = 102108

SET @PreferredEmail = IF (SELECT WorkEmailPreferred FROM tblContactPersonal WHERE CREF = @REF) =1
BEGIN
SELECT Email FROM tblContactPersonal WHERE CREF = @REF
END
ELSE
IF (SELECT PrivateEmailPreferred FROM tblContactPersonal WHERE CREF = @REF) = 1
BEGIN
SELECT PrivateEmail FROM tblContactPersonal WHERE CREF = @REF
END
ELSE
IF (SELECT Email FROM tblContactPersonal WHERE CREF = @REF) <> ''
BEGIN
SELECT Email FROM tblContactPersonal WHERE CREF = @REF
END
ELSE
IF (SELECT PrivateEmail FROM tblContactPersonal WHERE CREF = @REF) <> ''
BEGIN
SELECT PrivateEmail FROM tblContactPersonal WHERE CREF = @REF
END

I get an 'Incorrect syntax near the keyword 'IF' error.

 

[bborissov]

DECLARE @REF int
DECLARE @PreferredEmail as varchar(50)
SET @REF = 102108

IF EXISTS(SELECT WorkEmailPreferred FROM tblContactPersonal WHERE CREF = @REF)
   BEGIN
       SET @PreferredEmail = ????????
       SELECT Email FROM tblContactPersonal WHERE CREF = @REF
   END
ELSE IF EXISTS(SELECT PrivateEmailPreferred FROM tblContactPersonal WHERE CREF = @REF)
     BEGIN
        SELECT PrivateEmail FROM tblContactPersonal WHERE CREF = @REF
     END
ELSE IF EXISTS(SELECT Email FROM tblContactPersonal WHERE CREF = @REF)
     BEGIN
        SELECT Email FROM tblContactPersonal WHERE CREF = @REF
     END
ELSE IF EXISTS(SELECT PrivateEmail FROM tblContactPersonal WHERE CREF = @REF)
     BEGIN
        SELECT PrivateEmail FROM tblContactPersonal WHERE CREF = @REF
     END

Not tested!

Borislav Borissov
VFP9 SP2, SQL Server 2000/2005.

 

[Iamthestig]

Thanks alot Borislav, that's great!