TSQL - If..Else statement inside Table-Valued Functions - cant go through
Solution 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 infinitely1 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
Solution 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.
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 * 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.
Solution 5
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')
Comments
-
Artur over 3 years
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?
-
Ryk about 13 yearsFrom a SQL Optimiser perspective, this is probably the best solution.
-
Hogan about 13 years@Richard - What does my country have to do with it? The statement as written will look for values less than 154 when the parameter is 'low'. What exactly is your issue?
-
Hogan about 13 yearsI think you are missing an
END = 1
right before the return statement. -
RichardTheKiwi about 13 yearsYou made an unstated assumption that the height is an INTEGER value. Otherwise, your breaks do not match the OP's query.
-
RichardTheKiwi about 13 years@ryk - On SQL Server 2005, in a table with 1.1 million records, my answer came out faster. Query Plan shows 18%:82%, whereas statistics time shows 80ms:100ms
-
Hogan about 13 years@Richard - Ah I see... because he had
>= 155
and I had> 154
I added in non-integer values between like 154.5 Good point! -
Ryk about 13 years@Richard - so this is better?
-
RichardTheKiwi about 13 years@ryk / 18% and 80ms are for my answer. Less is better for both
-
Artur about 13 yearsThank for sharing your ideas !
-
Artur about 13 yearsThanks a lot Richard for your answer !
-
Doug_Ivison over 10 yearsGreat answer... since INLINE table-valued functions are parsed/cached (detailed query plans are generated for them) where multi-step table-valued functions (like the answers from Cade and Lamak) are not. P.S. He might resolve the gap between 175 & 180 by using the 175:
where ((@set = 'tall' and height > 175) or (@set = 'average' AND height >= 155 and height <= 175) or (@set = 'low' AND height < 155))