Clever Geek Handbook

Can we write an IN clause in an ELSE IF statement in SQL Server 2008

I am writing a scalar function and for this function I pass email and methodID and it will return INT and now I need to check the condition depending on methodID so that I return like below.

ALTER FUNCTION FN_EMPCHECK
(
    @EmailAddress VARCHAR(150),
    @methodID INT
)
RETURNS INT
AS
BEGIN
    DECLARE @IsResult INT

    IF(@methodID = 101)
    BEGIN
        SELECT @IsResult = ID FROM table1 WHERE EmailAddress = @EmailAddress
    END
    ELSE IF(@methodID = 102 OR @methodID = 104 OR @methodID = 105 OR @methodID = 107 OR @methodID = 108
                OR @methodID = 109 OR @methodID = 110 OR @methodID = 111 OR @methodID = 114)
    BEGIN
        SELECT @IsResult = ID FROM table2 WHERE EmailAddress = @EmailAddress
    END
    ELSE IF(@methodID = 103 OR @methodID = 106 OR @methodID = 112 OR @methodID = 113)
    BEGIN
        SELECT @IsResult = ID FROM table3 WHERE EmailAddress = @EmailAddress
    END

    RETURN @IsResult
END

Need suggestions for Else If the statement in the above weather function is correct or I can write in any other simple way (Writing many OR conditions)

+3


source to share


2 answers


You can use IN instead of multiple OR operators in your ELSE IF conditions, check below code



ALTER FUNCTION FN_EMPCHECK
(
    @EmailAddress VARCHAR(150),
    @methodID INT
)
RETURNS INT
AS
BEGIN
    DECLARE @IsResult INT

    IF(@methodID = 101)
    BEGIN
        SELECT @IsResult = ID FROM table1 WHERE EmailAddress = @EmailAddress
    END
    ELSE IF(@methodID IN (102, 104, 105, 107, 108, 109, 110, 111, 114))
    BEGIN
        SELECT @IsResult = ID FROM table2 WHERE EmailAddress = @EmailAddress
    END
    ELSE IF(@methodID IN (103 ,106, 112, 113))
    BEGIN
        SELECT @IsResult = ID FROM table3 WHERE EmailAddress = @EmailAddress
    END

    RETURN @IsResult
END
+2


source


Yes You can use IN in the If statement



ALTER FUNCTION FN_EMPCHECK
(
    @EmailAddress VARCHAR(150),
    @methodID INT
)
RETURNS INT
AS
BEGIN
DECLARE @IsResult INT

IF(@methodID = 101)
BEGIN
    SELECT @IsResult = ID FROM table1 WHERE EmailAddress = @EmailAddress
END
ELSE IF @methodID IN (102, 104, 105, 107, 108, 109, 110, 111, 114)
BEGIN
    SELECT @IsResult = ID FROM table2 WHERE EmailAddress = @EmailAddress
END
ELSE IF @methodID IN (103 ,106, 112, 113)
BEGIN
    SELECT @IsResult = ID FROM table3 WHERE EmailAddress = @EmailAddress
END

RETURN @IsResult
END
0


source






More articles:


All Articles