TSQL - If..Else语句在表值函数内 - 无法通过 [英] TSQL - If..Else statement inside Table-Valued Functions - cant go through

问题描述

在发布之前，我已经阅读了几篇关于开发USD函数的文章，但没有遇到我的问题的解决方案...，如下：

我有一个非常简单数据库，存储篮球运动员，由ID，年龄，高度和名称列组成。我想做的是用一个参数@set varchar（10）实现一个函数'height'，这取决于一个@set值会触发不同的select语句

我想要实现的是伪代码：

  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 
  

任何人都可以告诉我如何实现它吗？

解决方案

最简单的形式总是最好的

  CREATE FUNCTION [dbo]。[age]（@ set varchar（10））
 RETURNS TABLE 
 AS RETURN 
 SELECT * from player 
其中（（@set = high'and height> 180）
或（@set ='average'AND height> = 155 and height< = 175）
或（@set ='low'AND height< 155 ））
 GO 
  

这种形式称为INLINE表函数，这意味着SQL Server是免费的扩展它以将播放器直接连接到更大查询的其他表格，使得它比多语句表值函数更无限地执行

。

你可能更喜欢这个，所以你的范围是完整的（你有175和180之间的差距）

  where（（@set ='tall'and height> 180）
或（@set ='average'AND height> = 155 and height< = 180）
或（@set ='low'AND height< 155））
  

当解析变量@set时，SQL Server负责短路分支。 / p>

^{1夸张，但只是稍微}

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?

解决方案

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}

这篇关于TSQL - If..Else语句在表值函数内 - 无法通过的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！