TSQL - If..Else statement inside Table-Valued Functions - cant go through

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?

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.

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}

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

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.

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')

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

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.