blocks|key|1331204|text|在documentation中(假设您使用SQL-Server)：|type|unstyled|depth|inlineStyleRanges|entityRanges|offset|length|data|1331205|USE+AdventureWorks;
GO
DECLARE+@returnstatus+nvarchar(15);
SET+@returnstatus+=+NULL;
EXEC+@returnstatus+=+dbo.ufnGetSalesOrderStatusText+@Status+=+2;
PRINT+@returnstatus;
GO|code-block|syntax|javascript|1331206|所以，是的，它应该是这样工作的。|1331207|entityMap|0|LINK|mutability|MUTABLE|url|http://msdn.microsoft.com/en-us/library/ms188332.aspx^0|1|D|0|0|0|0^^$0|@$1|2|3|4|5|6|7|S|8|@]|9|@$A|T|B|U|1|V]]|C|$]]|$1|D|3|E|5|F|7|W|8|@]|9|@]|C|$G|H]]|$1|I|3|J|5|6|7|X|8|@]|9|@]|C|$]]|$1|K|3|-4|5|6|7|Y|8|@]|9|@]|C|$]]]|L|$M|$5|N|O|P|C|$Q|R]]]]

From the <a href="http://msdn.microsoft.com/en-us/library/ms188332.aspx" rel="noreferrer">documentation</a> (assuming that you use SQL-Server):

<pre><code>USE AdventureWorks;
GO
DECLARE @returnstatus nvarchar(15);
SET @returnstatus = NULL;
EXEC @returnstatus = dbo.ufnGetSalesOrderStatusText @Status = 2;
PRINT @returnstatus;
GO
</code></pre>

So yes, it should work that way.

blocks|key|1116210|text|我总是使用返回值来传回错误状态。如果需要传回一个值，我会使用输出参数。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1116211|示例存储过程，带有输出参数：|1116212|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|code-block|syntax|javascript|1116213|使用OUTPUT参数调用存储过程：|1116214|DECLARE+@OutputParameter++datetime
+++++++,@ReturnValue++++++int

EXEC+@ReturnValue=YourStoredProcedure+1,null,+@OutputParameter+OUTPUT
PRINT+@ReturnValue
PRINT+CONVERT(char(23),@OutputParameter+,121)|1116215|输出：|1116216|0
2010-01-01+00:00:00.000|1116217|entityMap^0|0|0|0|0|0|0|0^^$0|@$1|2|3|4|5|6|7|S|8|@]|9|@]|A|$]]|$1|B|3|C|5|6|7|T|8|@]|9|@]|A|$]]|$1|D|3|E|5|F|7|U|8|@]|9|@]|A|$G|H]]|$1|I|3|J|5|6|7|V|8|@]|9|@]|A|$]]|$1|K|3|L|5|F|7|W|8|@]|9|@]|A|$G|H]]|$1|M|3|N|5|6|7|X|8|@]|9|@]|A|$]]|$1|O|3|P|5|F|7|Y|8|@]|9|@]|A|$G|H]]|$1|Q|3|-4|5|6|7|Z|8|@]|9|@]|A|$]]]|R|$]]

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:

<pre><code>CREATE PROCEDURE YourStoredProcedure 
(
 @Param1 int
 ,@Param2 varchar(5)
 ,@Param3 datetime OUTPUT
)
AS
IF ISNULL(@Param1,0)&gt;5
BEGIN
 SET @Param3=GETDATE()
END
ELSE
BEGIN
 SET @Param3='1/1/2010'
END
RETURN 0
GO
</code></pre>

call to the stored procedure, with an OUTPUT parameter:

<pre><code>DECLARE @OutputParameter datetime
 ,@ReturnValue int

EXEC @ReturnValue=YourStoredProcedure 1,null, @OutputParameter OUTPUT
PRINT @ReturnValue
PRINT CONVERT(char(23),@OutputParameter ,121)
</code></pre>

OUTPUT:

<pre><code>0
2010-01-01 00:00:00.000
</code></pre>

blocks|key|1116262|text|如果您希望简单地返回一个整数，这将会起作用：|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1116263|DECLARE+@ResultForPos+INT+
EXEC+@ResultForPos+=+storedprocedureName+'InputParameter'
SELECT+@ResultForPos|code-block|syntax|javascript|1116264|entityMap^0|0|0^^$0|@$1|2|3|4|5|6|7|I|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|J|8|@]|9|@]|A|$E|F]]|$1|G|3|-4|5|6|7|K|8|@]|9|@]|A|$]]]|H|$]]

This will work if you wish to simply return an integer: 

<pre><code>DECLARE @ResultForPos INT 
EXEC @ResultForPos = storedprocedureName 'InputParameter'
SELECT @ResultForPos
</code></pre>

blocks|key|1331317|text|declare+@EventId+int

CREATE+TABLE+#EventId+(EventId+int)

insert+into+#EventId+exec+rptInputEventId

set+@EventId+=+(select+*+from+#EventId)

drop+table+#EventId+|type|code-block|depth|inlineStyleRanges|entityRanges|data|syntax|javascript|1331318|unstyled|entityMap^0|0^^$0|@$1|2|3|4|5|6|7|G|8|@]|9|@]|A|$B|C]]|$1|D|3|-4|5|E|7|H|8|@]|9|@]|A|$]]]|F|$]]

<pre><code>declare @EventId int

CREATE TABLE #EventId (EventId int)

insert into #EventId exec rptInputEventId

set @EventId = (select * from #EventId)

drop table #EventId 
</code></pre>

blocks|key|1331374|text|我也有同样的问题。虽然这里有很好的答案，但我决定创建一个表值函数。使用表(或标量)值函数，您不必更改存储的proc。我只是从表值函数中执行了一次select。请注意，参数(MyParameter是可选的)。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1331375|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;
)|code-block|syntax|javascript|1331376|要给你的变量赋值，你可以简单地这样做：|1331377|Declare+@MyDate+datetime;
SET+@MyDate+=+(SELECT+DateValue+FROM+MyDateFunction(@MyParameter));|1331378|您还可以使用标量值函数：|1331379|CREATE+FUNCTION+TestDateFunction()++
RETURNS+datetime++
BEGIN++
++++RETURN+(SELECT+GetDate());
END|1331380|然后你可以简单地做|1331381|Declare+@MyDate+datetime;
SET+@MyDate+=+(Select+dbo.TestDateFunction());
SELECT+@MyDate;|1331382|entityMap^0|0|0|0|0|0|0|0|0^^$0|@$1|2|3|4|5|6|7|U|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|V|8|@]|9|@]|A|$E|F]]|$1|G|3|H|5|6|7|W|8|@]|9|@]|A|$]]|$1|I|3|J|5|D|7|X|8|@]|9|@]|A|$E|F]]|$1|K|3|L|5|6|7|Y|8|@]|9|@]|A|$]]|$1|M|3|N|5|D|7|Z|8|@]|9|@]|A|$E|F]]|$1|O|3|P|5|6|7|10|8|@]|9|@]|A|$]]|$1|Q|3|R|5|D|7|11|8|@]|9|@]|A|$E|F]]|$1|S|3|-4|5|6|7|12|8|@]|9|@]|A|$]]]|T|$]]

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).

<pre><code>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;
)
</code></pre>

To assign to your variable you simply can do something like:

<pre><code>Declare @MyDate datetime;
SET @MyDate = (SELECT DateValue FROM MyDateFunction(@MyParameter));
</code></pre>

You can also use a scalar valued function:

<pre><code>CREATE FUNCTION TestDateFunction() 
RETURNS datetime 
BEGIN 
 RETURN (SELECT GetDate());
END
</code></pre>

Then you can simply do

<pre><code>Declare @MyDate datetime;
SET @MyDate = (Select dbo.TestDateFunction());
SELECT @MyDate;
</code></pre>

blocks|key|1116370|text|以下是动态查询的解决方案。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1116371|例如，如果您有更多具有不同后缀的表：|1116372|dbo.SOMETHINGTABLE_ONE,+dbo.SOMETHINGTABLE_TWO|code-block|syntax|javascript|1116373|代码：|1116374|DECLARE+@INDEX+AS+NVARCHAR(20)
DECLARE+@CheckVALUE+AS+NVARCHAR(max)+=+'SELECT+COUNT(SOMETHING)+FROM+
dbo.SOMETHINGTABLE_'%2B@INDEX%2B''
DECLARE+@tempTable+Table+(TempVALUE+int)
DECLARE+@RESULTVAL+INT

INSERT+INTO+@tempTable
++++EXEC+sp_executesql+@CheckVALUE

SET+@RESULTVAL+=+(SELECT+*+FROM+@tempTable)

DELETE+@tempTable

SELECT+@RESULTVAL+|1116375|entityMap^0|0|0|0|0|0^^$0|@$1|2|3|4|5|6|7|O|8|@]|9|@]|A|$]]|$1|B|3|C|5|6|7|P|8|@]|9|@]|A|$]]|$1|D|3|E|5|F|7|Q|8|@]|9|@]|A|$G|H]]|$1|I|3|J|5|6|7|R|8|@]|9|@]|A|$]]|$1|K|3|L|5|F|7|S|8|@]|9|@]|A|$G|H]]|$1|M|3|-4|5|6|7|T|8|@]|9|@]|A|$]]]|N|$]]

Here is solution for dynamic queries.
For example if you have more tables with different suffix:
<pre><code>dbo.SOMETHINGTABLE_ONE, dbo.SOMETHINGTABLE_TWO
</code></pre>
Code:
<pre><code>DECLARE @INDEX AS NVARCHAR(20)
DECLARE @CheckVALUE AS NVARCHAR(max) = 'SELECT COUNT(SOMETHING) FROM 
dbo.SOMETHINGTABLE_'+@INDEX+''
DECLARE @tempTable Table (TempVALUE int)
DECLARE @RESULTVAL INT

INSERT INTO @tempTable
 EXEC sp_executesql @CheckVALUE

SET @RESULTVAL = (SELECT * FROM @tempTable)

DELETE @tempTable

SELECT @RESULTVAL 
</code></pre>

How do you assign the result of an exec call to a variable in SQL? I have a stored proc called <code>up_GetBusinessDay</code>, which returns a single date. 

Can you do something like this:

<pre><code>exec @PreviousBusinessDay = dbo.up_GetBusinessDay @Date, -1
</code></pre>

How to assign an exec result to a sql variable?

如何将exec调用的结果赋值给SQL中的变量？我有一个名为up_GetBusinessDay的存储过程，它返回单个日期。你能做这样的事情吗：exec @PreviousBusinessDay = dbo.up_GetBusinessDay @Date, -1

问如何将exec结果赋值给sql变量？
EN

社区

活动

资源

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问如何将exec结果赋值给sql变量？EN

社区

活动

资源

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问如何将exec结果赋值给sql变量？
EN