sql 自定义函数 示例_SQL Server Choose（）函数介绍和示例

sql 自定义函数 示例

In the article, a CASE statement in SQL, we explored one of the important logical expressions – the Case statement in SQL Server to return the values based on the specified condition. Today, we will talk about another useful logical Choose() function in SQL Server 2012 onwards and its comparison with the Case statement.

在本文中， SQL中的CASE语句探讨了重要的逻辑表达式之一-SQL Server中的Case语句，用于根据指定条件返回值。 今天，我们将讨论SQL Server 2012及更高版本中另一个有用的逻辑Choose（）函数及其与Case语句的比较。

SQL中的CASE语句概述 (An Overview of the CASE statement in SQL )

Before we proceed with the Choose function, let’s have a quick overview of the Case statement in SQL

在继续执行Choose函数之前，让我们快速了解一下SQL中的Case语句

• It is a logical function, and we define conditions ( in When clause) and actions followed by Then clause
  这是一个逻辑函数，我们定义条件（在When子句中）和随后的Then子句的操作
• Once the expression or value satisfies in the when clause, it returns corresponding value or expression in the output
  表达式或值在when子句中满足后，它将在输出中返回相应的值或表达式
• If no conditions are satisfied, it returns the value specified in the Else clause
  如果不满足任何条件，则返回Else子句中指定的值

In the below example, we specify product id 1 in the variable, and it returns the output ‘Bread and Biscuits’.

在下面的示例中，我们在变量中指定产品ID 1，并返回输出“面包和饼干”。

SQL Server Choose（）函数简介 (Introduction to SQL Server Choose() function)

Suppose you need to specify multiple conditions in the case statement. In this case, the overall code will be lengthy. Sometimes, it is difficult to examine more extended code, and we always look for functions that could do similar work with minimum efforts and without any performance impact. Choose function does the same work for us and can be used as a replacement of Case statement.

假设您需要在case语句中指定多个条件。 在这种情况下，整个代码将很长。 有时，很难检查更多扩展的代码，并且我们总是寻找能够以最小的努力完成类似工作并且不影响性能的函数。 选择功能为我们完成了相同的工作，可以用作Case语句的替代。

选择功能的语法 (Syntax of Choose function)

We use Choose () function to return an item at a specific index position from the list of items.

我们使用Choose（）函数从项目列表中的特定索引位置返回项目。

Syntax of Choose function: CHOOSE ( index, value[1], value[2] ,….. value[N] )

选择函数的语法 ： CHOOSE（index，value [1]，value [2]，….. value [N]）

• Index: It is an integer that specifies the index position of the element we want in the output. Choose function does not use a zero-based indexing method. In this function, the first item starts at first, the second element at the 2nd index position, and so on. If we do not use an integer in this argument, SQL converts that into integer else returns a NULL value Index ：它是一个整数，指定我们想要的元素在输出中的索引位置。 选择函数不使用基于零的索引方法。 在此功能中，第一个项目首先开始，第二个元素在第二个索引位置，依此类推。 如果在此参数中不使用整数，SQL会将其转换为整数，否则返回NULL值
• Items: It is a comma-separated list of any type. Choose function picks the items as per the index defined in the first argument Items ：它是任何类型的逗号分隔列表。 选择函数根据第一个参数中定义的索引选择项目

For the index, choose function returns value[1] for index position 1. Let’s understand choose function in SQL using various examples.

对于索引，select函数返回索引位置1的value [1]。让我们使用各种示例来了解SQL中的select函数。

示例1：带文字值SQL Server CHOOSE（）函数 (Example 1: SQL Server CHOOSE() function with literal values)

In this example, we use choose() function to return values from different index positions. The first select statement returns NULL because this function does not use a zero indexing method.

在此示例中，我们使用choose（）函数从不同的索引位置返回值。 第一条选择语句返回NULL，因为此函数未使用零索引方法。

SELECT CHOOSE(0, 'Apple', 'Banana', 'Mango', 'Grapes', 'Strawberry') AS 'Zero Index value';
SELECT CHOOSE(1, 'Apple', 'Banana', 'Mango', 'Grapes', 'Strawberry') AS 'First Index value';
SELECT CHOOSE(2, 'Apple', 'Banana', 'Mango', 'Grapes', 'Strawberry') AS 'Second Index value';
SELECT CHOOSE(3, 'Apple', 'Banana', 'Mango', 'Grapes', 'Strawberry') AS 'Third Index value';

Similarly, if we choose out of range index value, it returns NULL in that case as well. For example, we have five records in the above list. Let’s specify the index position as six.

同样，如果我们选择超出范围的索引值，则在这种情况下它也会返回NULL。 例如，上面的列表中有五个记录。 让我们将索引位置指定为六个。

SELECT CHOOSE(6, 'Apple', 'Banana', 'Mango', 'Grapes', 'Strawberry') AS 'Sixth Index value';

示例2：具有十进制索引值SQL Server CHOOSE（）函数 (Example 2: SQL Server CHOOSE() function with decimal index values)

As specified earlier, we use integer data type in the first argument. Suppose you specify the index position as 2.5, what would be the output?

如前所述，我们在第一个参数中使用整数数据类型。 假设您将索引位置指定为2.5，输出是什么？

In the below, we specified multiple values in index argument having decimals. We get the same output from all the below queries. Choose() function rounds the decimal value towards lower value. In this case, all index values convert to 2 and return banana as output.

在下面，我们在索引参数中指定了多个带有小数的值。 我们从以下所有查询中获得相同的输出。 Choose（）函数将十进制值四舍五入到较低的值。 在这种情况下，所有索引值都将转换为2并返回香蕉作为输出。

SELECT CHOOSE(2.10, 'Apple', 'Banana', 'Mango', 'Grapes', 'Strawberry') AS '2.1 Index value';
SELECT CHOOSE(2.40, 'Apple', 'Banana', 'Mango', 'Grapes', 'Strawberry') AS '2.4 Index value';
SELECT CHOOSE(2.59, 'Apple', 'Banana', 'Mango', 'Grapes', 'Strawberry') AS '2.5 Index value';
SELECT CHOOSE(2.99, 'Apple', 'Banana', 'Mango', 'Grapes', 'Strawberry') AS '2.99 Index value';

示例3：SQL Server Choose（）函数的索引参数中的字符串值< (Example 3: String values in the index argument of SQL Server Choose() function<)

In this example, we specified index values in single quotes. It makes index argument as string values.

在此示例中，我们在单引号中指定了索引值。 它使索引参数成为字符串值。

SELECT CHOOSE('1', 'Apple', 'Banana', 'Mango', 'Grapes', 'Strawberry') AS 'Index value'
SELECT CHOOSE('5', 'Apple', 'Banana', 'Mango', 'Grapes', 'Strawberry') AS 'Index value';

SQL Server converts these string values into integers, and it works similar to specifying integer values as shown below.

SQL Server将这些字符串值转换为整数，并且其工作方式类似于指定整数值，如下所示。

However, if we specify strings such as ‘two’ in index argument, It results in an error message due to data type conversion failure.

但是，如果我们在索引参数中指定诸如“ two”之类的字符串，由于数据类型转换失败，将导致错误消息。

示例4：使用SQL Server Choose（）函数和数据类型优先级 (Example 4: Use SQL Server Choose() function and data type precedence)

Look at the following SQL code. In this, we specified integer and float data type values.

看下面SQL代码。 在此，我们指定了整数和浮点数据类型值。

SELECT CHOOSE(2, 5,10,3.69, 29) as [Output]

Choose() function returns the highest precedence data type in the output. A float or decimal value has high precedence over integer, so we get output in the high precedence data type as shown below.

Choose（）函数在输出中返回最高优先级的数据类型。 浮点或十进制值的优先级高于整数，因此我们以高优先级的数据类型获取输出，如下所示。

Let’s add a string ‘abc’, and we get the same output of Choose() function.

让我们添加一个字符串“ abc”，我们得到与Choose（）函数相同的输出。

SELECT CHOOSE(2, 5,10,3.69, 29,'ABC') as [Output]

If we specify index 5 that contains ‘abc’ you get an error message.

如果我们指定的索引5包含“ abc”，则会收到一条错误消息。

SELECT CHOOSE(5, 5,10,3.69, 29,'abc') as [Output]

We get this error because the SQL server is unable to change varchar data type to numeric data type having high precedence. You can refer to Data type precedence for more details.

我们收到此错误是因为SQL Server无法将varchar数据类型更改为具有较高优先级的数字数据类型。 您可以参考数据类型优先级以获取更多详细信息。

示例5：将SQL Server Choose（）函数与选择SQL语句一起使用 (Example 5: Use SQL Server Choose() function with Select SQL Statements)

In the previous examples, we understood the functionality of the SQL Choose() function. In this example, we use it in the select statement to retrieve records from the [AdventureWorks] database. It is a sample database for SQL Server, and you can download a backup copy from Microsoft Docs.

在前面的示例中，我们了解了SQL Choose（）函数的功能。 在此示例中，我们在select语句中使用它从[ AdventureWorks ]数据库中检索记录。 它是SQL Server的示例数据库，您可以从Microsoft Docs下载备份副本。

SELECT top 10 [NationalIDNumber]
,[JobTitle]
,[HireDate]
,[MaritalStatus]
  FROM [AdventureWorks].[HumanResources].[Employee]

We get employees’ records along with their hire date using the above query.

使用上面的查询，我们可以获得员工的记录以及他们的雇用日期。

Now, suppose we want to know the month for each employee. We can use the MONTH() function to return the month component from the date. In the below query, we specify month names and Choose() function returns the month from the list as per specified month in the index argument.

现在，假设我们想知道每个员工的月份。 我们可以使用MONTH（）函数从日期返回月份组成部分。 在下面的查询中，我们指定月份名称，并且Choose（）函数按照index参数中指定的月份从列表中返回月份。

SELECT top 10 [NationalIDNumber]
,[JobTitle]
,[HireDate]
,CHOOSE(MONTH([HireDate]),'January','February','March','April','May','June',
    'July','August','September','October','November','December') As [HireMonth]
,[MaritalStatus]
  FROM [AdventureWorks].[HumanResources].[Employee]

示例5：对JSON数据使用SQL Server Choose（）函数 (Example 5: Use SQL Server Choose() function with JSON data)

We can use the Choose() function to work with JSON data as well. You can understand JSON and its different functions using the SQLShack JSON language category.

我们也可以使用Choose（）函数来处理JSON数据。 您可以使用SQLShack JSON语言类别来了解JSON及其不同功能。

Here, we use a table-valued OPENJSON function. It returns the data type of the value in JSON data in the [type] column. In the below query, we use [type]+1 value in the index argument to retrieve the corresponding value from the list. We use +1 because [type] returns 0 for the NULL value but zero index position is not available in SQL Choose() function.

在这里，我们使用表值的OPENJSON函数。 它在[type]列中返回JSON数据中值的数据类型。 在下面的查询中，我们使用index参数中的[type] +1值从列表中检索相应的值。 我们使用+1是因为[type]对于NULL值返回0，但是SQL Choose（）函数中不提供零索引位置。

SELECT 
	[Key],
	[Value],
	[Type],
	CHOOSE(type+1,'Null','String','Integar','Boolean','JSON Array','JSON Object') AS JsonType
FROM
	OPENJSON(N'{
		"ID":null,
		"Name":"Rajendra",
		"EmpID":3,
		"IsActive":false,
		"Departments":["IT","Admin"],
		"EmployeeObject":{
			"City":"Delhi",
			"Country":"India"
		}
	}');

CASE语句与SQL Server中的Choose（）函数之间的比较 (Comparison between the CASE statement and Choose() function in SQL Server)

As you might be familiar with the SQL CASE statement and SQL Choose() function as of now. You might think, we can achieve the same results of SQL Choose() function from the SQL Case statement as well.

您可能现在已经熟悉SQL CASE语句和SQL Choose（）函数。 您可能会认为，我们也可以从SQL Case语句中获得与SQL Choose（）函数相同的结果。

Let’s convert the SQL query from example 5 above from SQL Choose() function to Case Statement in SQL to get the same results.

让我们将上面示例5中SQL查询从SQL Choose（）函数转换为SQL中的Case语句，以获得相同的结果。

SELECT TOP 10 [NationalIDNumber], 
    [JobTitle], 
    [HireDate],
    CASE(MONTH([HireDate]))
        WHEN 1
        THEN 'January'
        WHEN 2
        THEN 'February'
        WHEN 3
        THEN 'March'
        WHEN 4
        THEN 'April'
        WHEN 5
        THEN 'May'
        WHEN 6
        THEN 'July'
        WHEN 7
        THEN 'August'
        WHEN 8
        THEN 'September'
        WHEN 9a
        THEN 'October'
        WHEN 10
        THEN 'November'
        WHEN 11
        THEN 'November'
        WHEN 12
        THEN 'December'
    END 'HireMonth', 
    [MaritalStatus]
FROM [AdventureWorks].[HumanResources].[Employee];

The above query returns the same results as of SQL Choose function.

上面的查询返回与SQL Choose函数相同的结果。

You might think which one should we use? Is there any difference these two?

您可能会认为应该使用哪一个？ 这两个有什么区别吗？

To get the answers to these questions, run the query with SQL Choose() and Case statement in SQL together in a query window of SSMS. Use the Go statement to separate the batches. Enable the Actual Execution Plan (press CTRL+M) to compare both queries execution plans. You can also use the compare plan feature of SSMS for it. You can refer How to compare query execution plans in SQL Server 2016 for it.

要获得这些问题的答案，请在SSMS的查询窗口中使用SQL Choose（）和SQL中的Case语句一起运行查询。 使用Go语句分隔批次。 启用实际执行计划（按CTRL + M）以比较两个查询执行计划。 您也可以使用SSMS的比较计划功能。 您可以参考如何在SQL Server 2016中比较查询执行计划 。

SELECT TOP 10 [NationalIDNumber], 
    [JobTitle], 
    [HireDate],
    CASE(MONTH([HireDate]))
        WHEN 1
        THEN 'January'
        WHEN 2
        THEN 'February'
        WHEN 3
        THEN 'March'
        WHEN 4
        THEN 'April'
        WHEN 5
        THEN 'May'
        WHEN 6
        THEN 'July'
        WHEN 7
        THEN 'August'
        WHEN 8
        THEN 'September'
        WHEN 9
        THEN 'October'
        WHEN 10
        THEN 'November'
        WHEN 11
        THEN 'November'
        WHEN 12
        THEN 'December'
    END 'HireMonth', 
    [MaritalStatus]
FROM [AdventureWorks].[HumanResources].[Employee];
Go
SELECT top 10 [NationalIDNumber]
,[JobTitle]
,[HireDate]
,CHOOSE(MONTH([HireDate]),'January','February','March','April','May','June',
'July','August','September','October','November','December') As [HireMonth]
,[MaritalStatus]
FROM [AdventureWorks].[HumanResources].[Employee]

It is doing a clustered index scan for an index on [HumanResource].[Employee] table for both queries. The cost of both queries is also similar, as shown below.

它正在对两个查询的[HumanResource]。[Employee]表上的索引进行聚集索引扫描。 这两个查询的成本也相似，如下所示。

Now, let’s check the properties of compute scalar function in the actual execution plan of the SQL Choose() function.

现在，让我们在SQL Choose（）函数的实际执行计划中检查计算标量函数的属性。

It uses the Case statement in the background. It shows both Case statement in SQL and SQL Choose() functions are the same. You can use either of them, and it does not put any impact on query performance.

它在后台使用Case语句。 它表明SQL中的Case语句和SQL Choose（）函数都相同。 您可以使用它们中的任何一个，并且不会对查询性能产生任何影响。

The Choose() function is a shorthand of the Case statement. You can write smaller t-SQL codes to do similar using choose() function in comparison with the Case statement. We can see this difference in the SQL queries used for Case statement and SQL Choose(). You can also compare the other parameters in these plans, and all parameter looks similar.

Choose（）函数是Case语句的简写。 与Case语句相比，可以使用choice（）函数编写较小的t-SQL代码来执行类似的操作。 我们可以在用于Case语句和SQL Choose（）SQL查询中看到这种差异。 您还可以比较这些计划中的其他参数，并且所有参数看起来都相似。

结论 (Conclusion)

In this article, we explored the Choose() function in SQL Server to return the value based on the index position. In its comparison with a Case statement in SQL, we figured out that both functions are the same logically. If you use case statements in your query, I would recommend you to explore the Choose() function as well.

在本文中，我们探讨了SQL Server中的Choose（）函数以根据索引位置返回值。 通过与SQL中的Case语句进行比较，我们发现这两个函数在逻辑上是相同的。 如果在查询中使用案例语句，则建议您也探索Choose（）函数。

翻译自: https://www.sqlshack.com/sql-server-choose-function-introduction-and-examples/

sql 自定义函数 示例