Change the Table-Value Function to Inline from Multi-line

Question

I'm trying to change a Table-Valued Function to improve the performance of a Stored Procedure but I can't get the same result. Can you tell me where I'm going wrong?

Original:

create FUNCTION [dbo].[fun_eess1] (
@institucion int ,
@disa int,
@red int,
@mred int) RETURNS @tbl TABLE (idlista [int] IDENTITY, institucion [int], disa [int], red [int], mred [int], establecimiento varchar(10)) AS BEGIN
IF (@institucion=1) -- First Condition */ where @institucion=1 then UNION ALL
BEGIN
    INSERT INTO @tbl ([institucion], [disa], [red], [mred], [establecimiento])
    SELECT DISTINCT [c_institucion], [c_disa], [c_red], [c_microred], [codrenae] FROM [dbo].[RENAE]
    WHERE 

    [c_institucion]=@institucion AND [c_disa]=(CASE @disa WHEN -1 THEN [c_disa] ELSE @disa END)
    AND [c_red]=(CASE @red WHEN -1 THEN [c_red] ELSE @red END)
    AND [c_microred]=(CASE @mred WHEN -1 THEN [c_microred] ELSE @mred END)
END  ELSE
BEGIN
IF (@institucion=15) -- Second Condition */ where @institucion=15 then UNION ALL
BEGIN
    INSERT INTO @tbl ([institucion], [disa], [red], [mred], [establecimiento])
    SELECT DISTINCT [c_institucion], [c_disa], [c_red], [c_microred], [codrenae] FROM [dbo].[RENAE]
    WHERE [c_institucion]='1' AND [c_disa]=(CASE @disa WHEN -1 THEN [c_disa] ELSE @disa END)
    AND [c_red]=(CASE @red WHEN -1 THEN [c_red] ELSE @red END)
    AND [c_microred]=(CASE @mred WHEN -1 THEN [c_microred] ELSE @mred END)

    INSERT INTO @tbl ([institucion], [disa], [red], [mred], [establecimiento])
    SELECT [org].[CodigoOrganizacion], [di].[iddisa], [r].[idred], [mi].[idmred], CAST ([es].[idestablecimiento] AS varchar) FROM [dbo].[Establecimientos] es
    INNER JOIN [dbo].[microred] mi ON [mi].[idmred]=[es].[microred]
    INNER JOIN [dbo].[Red] r ON [mi].[red] = [r].[idred]
    INNER JOIN [dbo].[Disa] di ON [r].[disa] = [di].[iddisa]
    INNER JOIN [dbo].[Organizacion] org ON [di].[institucion] = [org].[CodigoOrganizacion]
    WHERE [org].[CodigoOrganizacion] in ('2','3','4','5','6','13') AND [di].[iddisa]=(case @disa when -1 then [iddisa] else @disa end) AND [r].[idred]=(case @red when -1 then [idred] else @red end)  AND [mi].[idmred]=(case @mred when -1 then [idmred] else @mred end)
END
ELSE
BEGIN  -- Third Condition? */ where @institucion is not (1,15) then UNION ALL
    INSERT INTO @tbl ([institucion], [disa], [red], [mred], [establecimiento])
    SELECT [org].[CodigoOrganizacion], [di].[iddisa], [r].[idred], [mi].[idmred], CAST ([es].[idestablecimiento] AS varchar) FROM [dbo].[Establecimientos] es
    INNER JOIN [dbo].[microred] mi ON [mi].[idmred]=[es].[microred]
    INNER JOIN [dbo].[Red] r ON [mi].[red] = [r].[idred]
    INNER JOIN [dbo].[Disa] di ON [r].[disa] = [di].[iddisa]
    INNER JOIN [dbo].[Organizacion] org ON [di].[institucion] = [org].[CodigoOrganizacion]
    WHERE [org].[CodigoOrganizacion]=@institucion AND [di].[iddisa]=(case @disa when -1 then [iddisa] else @disa end) AND [r].[idred]=(case @red when -1 then [idred] else @red end)  AND [mi].[idmred]=(case @mred when -1 then [idmred] else @mred end) 
END END

Hope someone can help me!

Answer 1

You can change it by creating one big query using union all, and moving the conditions from the if expressions to the where clauses of the inner queries. Also, instead of the Identity column in the return table, wrap the entire union all in another query and select row_number over(order by @@spid) (which returns an arbitrary raw number since it's ordered by a constant):

CREATE FUNCTION [dbo].[fun_eess1] 
(
    @institucion int ,
    @disa int,
    @red int,
    @mred int
)

RETURNS TABLE 

AS BEGIN

    SELECT ROW_NUMBER() OVER(ORDER BY @@SPID) As idlista, *
    FROM (
        SELECT DISTINCT 
                [c_institucion] As [institucion], 
                [c_disa] As [disa], 
                [c_red] AS [red], 
                [c_microred] AS [mred], 
                [codrenae] AS [establecimiento]
        FROM [dbo].[RENAE]
        WHERE @institucion=1 -- First condition
        AND [c_institucion]=@institucion 
        AND [c_disa] = CASE @disa WHEN -1 THEN [c_disa] ELSE @disa END
        AND [c_red] = CASE @red WHEN -1 THEN [c_red] ELSE @red END
        AND [c_microred] = CASE @mred WHEN -1 THEN [c_microred] ELSE @mred END

        UNION ALL

        SELECT DISTINCT [c_institucion], [c_disa], [c_red], [c_microred], [codrenae] 
        FROM [dbo].[RENAE]
        WHERE @institucion=15 -- Second condition
        AND [c_institucion]='1' 
        AND [c_disa] = CASE @disa WHEN -1 THEN [c_disa] ELSE @disa END
        AND [c_red] = CASE @red WHEN -1 THEN [c_red] ELSE @red END
        AND [c_microred] = CASE @mred WHEN -1 THEN [c_microred] ELSE @mred END

        UNION ALL

        SELECT [org].[CodigoOrganizacion], [di].[iddisa], [r].[idred], [mi].[idmred], CAST ([es].[idestablecimiento] AS varchar) 
        FROM [dbo].[Establecimientos] es
        INNER JOIN [dbo].[microred] mi ON [mi].[idmred]=[es].[microred]
        INNER JOIN [dbo].[Red] r ON [mi].[red] = [r].[idred]
        INNER JOIN [dbo].[Disa] di ON [r].[disa] = [di].[iddisa]
        INNER JOIN [dbo].[Organizacion] org ON [di].[institucion] = [org].[CodigoOrganizacion]
        WHERE 
        (
            (
                @institucion=15 -- Second condition
                AND [org].[CodigoOrganizacion] in ('2','3','4','5','6','13')
            )
            OR 
            (
                @institucion NOT IN(1, 15) -- Third condition
                AND [org].[CodigoOrganizacion] = @institucion
            )
        )
        AND [di].[iddisa] = case @disa when -1 then [iddisa] else @disa end  
        AND [r].[idred] = case @red when -1 then [idred] else @red end 
        AND [mi].[idmred]= case @mred when -1 then [idmred] else @mred end

    )

END