我该如何从SQL Server中的用户定义函数返回多个值(例如一个数字和一个字符串)?
我该如何从SQL Server中的用户定义函数返回多个值(例如一个数字和一个字符串)?
将其更改为表值函数。
请参考以下链接,例如。
这是一个内联函数的查询分析器模板 - 默认情况下,它返回2个值:
-- =============================================
-- Create inline function (IF)
-- =============================================
IF EXISTS (SELECT *
FROM sysobjects
WHERE name = N'<inline_function_name, sysname, test_function>')
DROP FUNCTION <inline_function_name, sysname, test_function>
GO
CREATE FUNCTION <inline_function_name, sysname, test_function>
(<@param1, sysname, @p1> <data_type_for_param1, , int>,
<@param2, sysname, @p2> <data_type_for_param2, , char>)
RETURNS TABLE
AS
RETURN SELECT @p1 AS c1,
@p2 AS c2
GO
-- =============================================
-- Example to execute function
-- =============================================
SELECT *
FROM <owner, , dbo>.<inline_function_name, sysname, test_function>
(<value_for_@param1, , 1>,
<value_for_@param2, , 'a'>)
GO
正如用户 Mr. Brownstone
建议的那样,您可以使用{{link1:存储过程}};为了方便起见,我创建了一个最简示例。首先{{link2:创建一个存储过程}}:
Create PROCEDURE MultipleOutParameter
@Input int,
@Out1 int OUTPUT,
@Out2 int OUTPUT
AS
BEGIN
Select @Out1 = @Input + 1
Select @Out2 = @Input + 2
Select 'this returns your normal Select-Statement' as Foo
, 'amazing is it not?' as Bar
-- Return can be used to get even more (afaik only int) values
Return(@Out1+@Out2+@Input)
END
DECLARE @GetReturnResult int, @GetOut1 int, @GetOut2 int
EXEC @GetReturnResult = MultipleOutParameter
@Input = 1,
@Out1 = @GetOut1 OUTPUT,
@Out2 = @GetOut2 OUTPUT
Select @GetReturnResult as ReturnResult, @GetOut1 as Out_1, @GetOut2 as Out_2