带有返回 2 个值的函数的 Sql 查询
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/8903441/
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
Sql query with function that return 2 values
提问by John
How would I return multiple values (say, a number and a string) from a user-defined function in SQL Server inside a query?
我将如何从查询中 SQL Server 中的用户定义函数返回多个值（例如，一个数字和一个字符串）？
e.g select col1, dbo.function1(col2) from table
例如 select col1, dbo.function1(col2) from table
and the result to be
结果是
Col1 | Col2 | Col3
-----+-------------------+---------------------
x | Num from function | String from function
I don't want to call the function 2 times because i use some complex code and i don't want to execute it twice. Just to take the final 2 results in the Return
我不想调用该函数 2 次，因为我使用了一些复杂的代码并且我不想执行它两次。只是为了在 Return 中取最后 2 个结果
回答by vmvadivel
Extract from - http://www.sqlteam.com/article/returning-complex-data-from-user-defined-functions-with-cross-apply
摘自 - http://www.sqlteam.com/article/returning-complex-data-from-user-defined-functions-with-cross-apply
Returning multiple values from a UDF can be done fairly easily in SQL Server, but we must return those values as a virtual table
在 SQL Server 中可以很容易地从 UDF 返回多个值，但我们必须将这些值作为虚拟表返回
--Sample Table
CREATE TABLE emails
(
ID INT PRIMARY KEY,
EmailAddress VARCHAR(100)
)
GO
--Dummy Data
INSERT INTO emails
SELECT 1,'[email protected]' UNION ALL
SELECT 2,'[email protected]' UNION ALL
SELECT 3,'[email protected]'
--UDF Creation
CREATE FUNCTION EmailParse (@email VARCHAR(1000))
RETURNS @t TABLE (UserName VARCHAR(20), Domain VARCHAR(20))
AS
BEGIN
DECLARE @i INT
SELECT @i = charindex('@', @email,1);
IF (@i > 1)
INSERT INTO @t VALUES (LEFT(@email,@i-1), SUBSTRING(@email,@i+1,20))
ELSE
INSERT INTO @t VALUES (NULL,NULL)
RETURN
END
--UDF Usage
SELECT
emails.ID, s.Username, s.Domain
FROM
emails
CROSS APPLY
EmailParse(emails.EmailAddress) s
回答by Abe Miessler
I would probably create a table UDF. Something along the lines of this:
我可能会创建一个表 UDF。类似的东西：
CREATE FUNCTION [owner].[function_name]
(
@parm1 <datatpe> = <default>
)
RETURNS TABLE
AS
RETURN
(
SELECT <column1, column2, ...>
FROM <table, view, etc.>
WHERE <some condition applies> -- optional clauses
)
more info here.
更多信息在这里。
It might seem like kind of a waste if you are only returning one row, but I think your alternatives(xml, parsing values from string on the fly) would make things much harder .
如果您只返回一行，这似乎是一种浪费，但我认为您的替代方案（xml，动态解析字符串中的值）会使事情变得更加困难。
回答by John Woo
You can'tcreate a function that returns two values. What's the problem when you call the function twice?
您不能创建返回两个值的函数。当您调用该函数两次时有什么问题？
i have found an article in TechNet which explains how to create a Table-Valued User-Defined Functions.
我在 TechNet 中找到了一篇文章，其中解释了如何创建表值用户定义函数。
