Set a SP return value to a variable in SQL Server

Question

Hi, 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:

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

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

thanks!

Answer 1

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

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

Answer 2

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

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