Tsql - If..else Statement Inside Table-valued Functions - Cant Go Through

March 27, 2024 Post a Comment

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, whi

Solution 1:

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

CREATEFUNCTION [dbo].[age](@setvarchar(10))
RETURNS@PlayersTABLE
(
    -- Put the players table definition here
) 
ASBEGIN

    IF  (@set='tall')
         INSERTINTO@PlayersSELECT*from player where height >180ELSE IF (@set='average')
         INSERTINTO@PlayersSELECT*from player where height >=155and height <=175ELSE IF (@set='low')
         INSERTINTO@PlayersSELECT*from player where height <155RETURN-- @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.

Solution 2:

The simplest form is always the best

CREATEFUNCTION[dbo].[age](@set varchar(10))
RETURNSTABLEASRETURNSELECT * fromplayerwhere ((@set = 'tall'andheight > 180)
   or (@set = 'average'ANDheight >= 155andheight <=175)
   or (@set = 'low'ANDheight < 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'andheight > 180)
   or (@set = 'average'ANDheight >= 155andheight <= 180)
   or (@set = 'low'ANDheight < 155))

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

Solution 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

Solution 4:

This should work.

SELECT * FROMplayerWHEREheight > CASEWHEN@set = 'tall' THEN 180WHEN@set = 'average' THEN 154WHEN@set = 'low' THEN 0
          END

I'll leave the < case for your enjoyment.

Solution 5:

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

CREATEfunction[dbo].[AA] 
(
@abc varchar(10)
)
Returns  @mytabletable
(
supname nvarchar(10), [add] nvarchar(10)
)
ASbegin--lOADWHATEVERTHINGSYOUREQUIREDINTOTHISDYNAMICTABLEif (@abc ='hh')
    insertinto @mytable (supname, [add]) values ('hh','gg'+ @abc)
elseinsertinto @mytable (supname, [add]) values ('else','gg'+ @abc)
returnend

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

Lab Sql