Function with if and multy else with different return SQL Server

Question

I created a function that searches for a particular value by priority. That is to say, if I found him then I would return the answer. If not continue to search in another way.

The problem is that the function, even if it finds an object at the beginning, does all the following SELECT's and does not stop at the first and returns it only.

CREATE FUNCTION GetId 
    (@A NVARCHAR(9),
     @B NVARCHAR(9),
     @C NVARCHAR(2)
    )
RETURNS INT
AS 
BEGIN
    DECLARE @ResId INT = NULL

    SET @ResId = (SELECT TOP 1 id 
                  FROM MyTable
                  WHERE (Filed1 = @A AND Filed2 = @B
                         OR Filed1 = @B AND Filed2 = @A)
                    AND Filed3 = @C
                    AND Filed4 = 1)
    IF @ResId != NULL
        RETURN @ResId
    ELSE
       SET @ResId = (SELECT TOP 1 id 
                     FROM MyTable
                     WHERE (Filed1 = @A OR Filed2 = @A)
                       AND Filed3 = @C
                       AND Filed4 = 1)
       IF @ResId != NULL
           RETURN @ResId
       ELSE 
           SET @ResId = (SELECT TOP 1 id 
                         FROM MyTable
                         WHERE (Filed1 = @B OR Filed2 = @B)
                           AND Filed3 = @C
                           AND Filed4 = 1)
       IF @ResId != NULL
           RETURN @ResId

    RETURN @ResId
END

@Gordon Linoff is right on the money. This SO question has a great answer which provides a little context as to why this is the case. https://stackoverflow.com/questions/1843451/why-does-null-null-evaluate-to-false-in-sql-server — Dwight Reynoldson, Dec 10 '18 at 12:44
Also: `TOP 1` *without* an explicit `ORDER BY` is pretty pointless - you'll get *one arbitrary* row - not sure which one; you should **always** define an `ORDER BY` when using `TOP` !! — marc_s, Dec 10 '18 at 12:57

score -2 · Answer 1 · answered Dec 10 '18 at 12:42

create FUNCTION GetId 
( @A nvarchar(9),
  @B nvarchar(9),
  @C nvarchar(2)
)
RETURNS int
AS BEGIN

declare @ResId int = null

set @ResId= (select top 1 id from MyTable
              where (Filed1 = @A and Filed2 = @B
                 or Filed1 = @B and Filed2 = @A)
                and Filed3 = @C
                and Filed4 = 1)
if @ResId is not NULL
   RETURN @ResId
else
   set @ResId=(select top 1 id from MyTable
                where (Filed1 = @A
                   or Filed2 = @A)
                  and Filed3 = @C
                  and Filed4 = 1)
if @ResId is not null
   RETURN @ResId
else
   set @ResId=(select top 1 id from MyTable
                where (Filed1 = @B
                   or Filed2 = @B)
                  and Filed3 = @C
                  and Filed4 = 1)
if @ResId is not null
   RETURN @ResId


RETURN @ResId

END

Dont provide the answer yourself in a comment. – SqlKindaGuy Dec 10 '18 at 12:49 — SqlKindaGuy, Dec 10 '18 at 12:49

Function with if and multy else with different return SQL Server

1 Answers1