如何将 exec 结果分配给 sql 变量？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/2245691/
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
How to assign an exec result to a sql variable?
提问by Prabhu
How do you assign the result of an exec call to a variable in SQL? I have a stored proc called up_GetBusinessDay, which returns a single date.
如何将 exec 调用的结果分配给 SQL 中的变量？我有一个名为 的存储过程up_GetBusinessDay，它返回一个日期。
Can you do something like this:
你能做这样的事情吗：
exec @PreviousBusinessDay = dbo.up_GetBusinessDay @Date, -1
采纳答案by KM.
I always use the return value to pass back error status. If you need to pass back one value I'd use an output parameter.
我总是使用返回值来回传错误状态。如果您需要传回一个值，我会使用输出参数。
sample stored procedure, with an OUTPUT parameter:
示例存储过程，带有一个 OUTPUT 参数：
CREATE PROCEDURE YourStoredProcedure
(
@Param1 int
,@Param2 varchar(5)
,@Param3 datetime OUTPUT
)
AS
IF ISNULL(@Param1,0)>5
BEGIN
SET @Param3=GETDATE()
END
ELSE
BEGIN
SET @Param3='1/1/2010'
END
RETURN 0
GO
call to the stored procedure, with an OUTPUT parameter:
使用 OUTPUT 参数调用存储过程：
DECLARE @OutputParameter datetime
,@ReturnValue int
EXEC @ReturnValue=YourStoredProcedure 1,null, @OutputParameter OUTPUT
PRINT @ReturnValue
PRINT CONVERT(char(23),@OutputParameter ,121)
OUTPUT:
输出：
0
2010-01-01 00:00:00.000
回答by Siddhesh Bondre
This will work if you wish to simply return an integer:
如果您只想返回一个整数，这将起作用：
DECLARE @ResultForPos INT
EXEC @ResultForPos = storedprocedureName 'InputParameter'
SELECT @ResultForPos
回答by AZ Chad
declare @EventId int
CREATE TABLE #EventId (EventId int)
insert into #EventId exec rptInputEventId
set @EventId = (select * from #EventId)
drop table #EventId
回答by Peter Lang
From the documentation(assuming that you use SQL-Server):
从文档（假设您使用 SQL-Server）：
USE AdventureWorks;
GO
DECLARE @returnstatus nvarchar(15);
SET @returnstatus = NULL;
EXEC @returnstatus = dbo.ufnGetSalesOrderStatusText @Status = 2;
PRINT @returnstatus;
GO
So yes, it should work that way.
所以是的，它应该这样工作。
回答by CodeCaptain
I had the same question. While there are good answers here I decided to create a table-valued function. With a table (or scalar) valued function you don't have to change your stored proc. I simply did a select from the table-valued function. Note that the parameter (MyParameter is optional).
我有同样的问题。虽然这里有很好的答案，但我决定创建一个表值函数。使用表（或标量）值函数，您不必更改存储过程。我只是从表值函数中进行了选择。注意参数（MyParameter 是可选的）。
CREATE FUNCTION [dbo].[MyDateFunction]
(@MyParameter varchar(max))
RETURNS TABLE
AS
RETURN
(
--- Query your table or view or whatever and select the results.
SELECT DateValue FROM MyTable WHERE ID = @MyParameter;
)
To assign to your variable you simply can do something like:
要分配给您的变量，您只需执行以下操作：
Declare @MyDate datetime;
SET @MyDate = (SELECT DateValue FROM MyDateFunction(@MyParameter));
You can also use a scalar valued function:
您还可以使用标量值函数：
CREATE FUNCTION TestDateFunction()
RETURNS datetime
BEGIN
RETURN (SELECT GetDate());
END
Then you can simply do
然后你可以简单地做
Declare @MyDate datetime;
SET @MyDate = (Select dbo.TestDateFunction());
SELECT @MyDate;
