Set a SP return value to a variable in SQL Server

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!

From stackoverflow Bruno

• 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
  

  Bruno : the problem is that I can't change the procedure's code...
  From Tim C

• 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
  

  brianb : Would be very handy (if you had permissions) to set this up as your own stored proc with more value. Chances are you're going to want to call this multiple times.
  From Tim C

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

  From DiGi