本文介绍了TSQL - If..Else语句在表值函数内 - 无法通过的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!

问题描述

在发布之前,我已经阅读了几篇关于开发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>


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.

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

08-22 13:05