SQL TSQL - 表值函数中的 If..Else 语句 - 无法通过
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/5544269/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
TSQL - If..Else statement inside Table-Valued Functions - cant go through
提问by Artur
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
我有一个非常简单的数据库，它存储篮球运动员并由 ID、年龄、身高和姓名列组成。我想要做的是用一个参数@set varchar(10) 实现一个函数“高度”，根据一个@set 值将触发不同的选择语句
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?
谁能给我一个提示如何实现它？
回答by Cade Roux
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.
我建议使用内联 TVF，如 Richard 的回答所示。它可以从您的语句中推断出表返回。
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.
另请注意，多语句和内联 TVF 确实非常不同。内联 TVF 对优化器来说不是一个黑盒，更像是一个参数化视图，因为优化器能够在同一执行计划中重新排列其他表和视图的内容。
回答by RichardTheKiwi
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 infinitely1betterthan a multi-statement table valued function.
这种形式称为 INLINE 表函数，这意味着 SQL Server 可以自由扩展它以将 player 直接连接到更大查询的内联其他表，使其性能比多语句表值函数好无限1。
You may prefer this though, so that your ranges are complete (you have a gap between 175 and 180)
不过，您可能更喜欢这个，以便您的范围完整（您在 175 和 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.
当解析变量@set 时，SQL Server 负责短路分支。
1 exaggeration, but only slightly
1 夸张，但只是轻微
回答by SQLMenace
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
回答by Hogan
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.
我会留下 < 案例供您欣赏。
回答by user2316060
We can use Table valued function in following way with IF conditions on it.
我们可以按以下方式使用表值函数，并带有 IF 条件。
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')
回答by Lamak
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
回答by CubeSpark
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..."
根据 Itzik Ben-Gan 在他的书“TSQL Querying”（Itzik Ben-Gan等人，(c) 2015 Microsoft Press，ISBN 978-0-7356-8504-8，P. 215）中所说的“......我发现内联TVFs 是一个很好的工具，允许封装逻辑和可重用性，而不会出现 UDF 的任何性能问题......”
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)."
他还说，如果您需要“ ……一个可重用的表表达式，如视图，但您还需要将输入参数传递给表表达式……TSQL 提供了内联表值函数 (TVF)。”
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.
这种类型的“IF”（内联函数 - sys.objects 中的一种不同类型）使用“RETURNS TABLE”输出说明符，并且似乎不能包含 BEGIN / END。语法和限额非常严格，但我们看到了良好的优化和性能。这些因素由@ryk 看到的时间指示。
