将SP返回值设置为SQL Server中的变量

I have a sproc that returns a single line and column with a text, I need to set this text to a variable, something like:

我有一个带有文本的单行和列的sproc，我需要将此文本设置为变量，如：

declare @bla varchar(100)
select @bla = sp_Name 9999, 99989999, 'A', 'S', null

but of course, this code doesn't work...

但当然，这段代码不起作用......

thanks!

谢谢！

3 个解决方案

#1

If you are unable to change the stored procedure, another solution would be to define a temporary table, and insert the results into that

如果您无法更改存储过程，则另一种解决方案是定义临时表，并将结果插入其中

DECLARE @Output VARCHAR(100)

CREATE TABLE #tmpTable
(
    OutputValue VARCHAR(100)
)
INSERT INTO #tmpTable (OutputValue)
EXEC dbo.sp_name 9999, 99989999, 'A', 'S', null

SELECT
    @Output = OutputValue
FROM 
    #tmpTable

DROP TABLE #tmpTable

#2

If the stored procedure is returning a single value you could define one of the parameters on the stored procedure to be an OUTPUT variable, and then the stored procedure would set the value of the parameter

如果存储过程返回单个值，您可以将存储过程中的一个参数定义为OUTPUT变量，然后存储过程将设置参数的值

CREATE PROCEDURE dbo.sp_Name
    @In INT,
    @Out VARCHAR(100) OUTPUT

AS
BEGIN
    SELECT @Out = 'Test'
END
GO

And then, you get the output value as follows

然后，您获得如下输出值

DECLARE @OUT VARCHAR(100)
EXEC sp_name 1, @Out OUTPUT
PRINT @Out

#3

DECLARE
  @out INT

EXEC @out = sp_name 'param', 2, ...

More info in T-SQL "EXECUTE" help (Help is from MSSQL 2008 but this works in 2000 too)

T-SQL“EXECUTE”帮助中的更多信息（帮助来自MSSQL 2008，但这也适用于2000）

#1