TSQL - If..Else statement inside Table-Valued Func

问题:

Before posting I have read few articles about developing USD functions, but have not encountered solutions for my problem... which is as follows:

I have a very simple database, which stores basketball players and consists of ID, Age, Height and Name column. What I would like to do is to implement a function 'height' with one parameter @set varchar(10), that depending one @set value will trigger off different select statements

what I was trying to implement was in psuedo-code:

CREATE FUNCTION [dbo].[age](@set varchar(10))
RETURNS TABLE
AS
BEGIN

    IF  (@set = 'tall')
         SELECT * from player where height > 180

    ELSE IF (@set = 'average')
         SELECT * from player where height >= 155 and height <=175

    ELSE IF (@set = 'low')
         SELECT * from player where height < 155
END

Could anyone give me a hint how to implement it?

回答1:

The simplest form is always the best

CREATE FUNCTION [dbo].[age](@set varchar(10))
RETURNS TABLE
AS RETURN
SELECT * from player
where ((@set = 'tall' and height > 180)
   or (@set = 'average' AND height >= 155 and height <=175)
   or (@set = 'low' AND height < 155))
GO

This form is called INLINE table function, which means SQL Server is free to expand it to join player directly to other tables in-line of a greater query, making it perform infinitely¹ better than a multi-statement table valued function.

You may prefer this though, so that your ranges are complete (you have a gap between 175 and 180)

where ((@set = 'tall' and height > 180)
   or (@set = 'average' AND height >= 155 and height <= 180)
   or (@set = 'low' AND height < 155))

SQL Server takes care of short circuiting the branches when the variable @set is parsed.

^{1 exaggeration, but only slightly}

回答2:

You were close. Using a multi-statement table-valued function requires the return table to be specified and populated in the function:

CREATE FUNCTION [dbo].[age](@set varchar(10))
RETURNS @Players TABLE
(
    -- Put the players table definition here
) 
AS
BEGIN

    IF  (@set = 'tall')
         INSERT INTO @Players SELECT * from player where height > 180

    ELSE IF (@set = 'average')
         INSERT INTO @Players SELECT * from player where height >= 155 and height <=175

    ELSE IF (@set = 'low')
         INSERT INTO @Players SELECT * from player where height < 155

    RETURN -- @Players (variable only required for Scalar functions)

END

I would recommend using an inline TVF as Richard's answer demonstrates. It can infer the table return from your statement.

Note also that a multi-statement and inline TVFs are really quite different. An inline TVF is less of a black-box to the optimizer and more like a parametrized view in terms of the optimizer being able to rearrange things with other tables and views in the same execution plan.

回答3:

Why are you hardcoding this, create a heights table and then grab all the heights that are valid for the range

SELECT * from player p
join Heights h on p.height between h.heightStart and h.heightEnd 
WHERE h.height  = @set

回答4:

This should work.

SELECT * FROM player 
WHERE
  height > CASE 
            WHEN @set = 'tall' THEN 180
            WHEN @set = 'average' THEN 154
            WHEN @set = 'low' THEN 0
          END

I'll leave the < case for your enjoyment.

回答5:

Something like this:

CREATE FUNCTION [dbo].[Age](@set VARCHAR(10))  
RETURNS @Players TABLE
(
    playerId INT,
    Name VARCHAR(50)
) 
AS  
BEGIN 

    INSERT INTO @Players
    SELECT playerId, Name
    FROM player 
    WHERE CASE WHEN @set = 'tall' AND height > 180 THEN 1
    WHEN @set = 'average' AND height BETWEEN 155 AND 180 THEN 1
    WHEN @set = 'low' AND height < 155 THEN 1 ELSE 0 END = 1

    RETURN
END

回答6:

We can use Table valued function in following way with IF conditions on it.

CREATE function [dbo].[AA] 
(
@abc varchar(10)
)
Returns  @mytable table
(
supname nvarchar(10), [add] nvarchar(10)
)
AS
begin
-- lOAD WHATEVER THINGS YOU REQUIRED INTO THIS DYNAMIC TABLE
if (@abc ='hh')
    insert into @mytable (supname, [add]) values ('hh','gg'+ @abc)
else
    insert into @mytable (supname, [add]) values ('else','gg'+ @abc)
return
end

---

--select * from [dbo].[AA]('SDAASF')

回答7:

According to Itzik Ben-Gan in his book "TSQL Querying" (Itzik Ben-Gan et al, (c) 2015 Microsoft Press, ISBN 978-0-7356-8504-8, P. 215) "...I find inline TVFs to be a great tool, allowing for the encapsulation of the logic and reusability without any performance problems of UDF's..."

He says also that if you need "...a reusable table expression like a View, but you also need to pass input parameters to the table expression...TSQL provides inline table-valued functions (TVFs). "

This type of 'IF' ( inline function - a distinct type in sys.objects ) uses the 'RETURNS TABLE' output specifier and seemingly cannot contain BEGIN / END. The syntax and allowances are very restrictive, yet we see good optimization and performance. These factors are indicated by the timings seen by @ryk.