Set a SP return value to a variable in SQL Server

Question

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