首页 文章

使用if和multy的函数与不同的返回SQL Server

提问于
浏览
0

我创建了一个按优先级搜索特定值的函数 . 也就是说,如果我找到他,那么我会回答答案 . 如果没有继续以另一种方式搜索 .

问题是该函数即使在开头找到一个对象,也执行以下所有SELECT,并且不会在第一个停止并仅返回它 .

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

1 回答

  • -2
    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
    

相关问题