title: Can We Store output value from EXECUTE Command into a variable?
body: <p>I want to store the JSON output of a dynamic stored procedure into a variable so that I can use that output in another stored procedure.</p>
  <p>Here is what I'm trying to do: I have a stored procedure that accepts three parameters</p>
  <pre><code>@CompId, @LanguageCode, @Address OUTPUT
  </code></pre>
  <pre><code>ALTER PROCEDURE [PL].[spGetCompanyAddress]
      @CompId INT,
      @LanguageCode VARCHAR(2),
      @Address VARCHAR(MAX) OUTPUT
  AS
  BEGIN
  BEGIN TRY
      SET NOCOUNT ON;
  
      DECLARE @UserName VARCHAR(100) = (SELECT [pl].fnFormattedname(@CompId, @CompId,0))
      DECLARE @MultilingualStateName VARCHAR(200) = (dbo.fnGetMultilingualColumns('States', @LanguageCode, 'States'))
  
      SET @Address = N'SELECT ISNULL((SELECT '
      + '[pl].fnFormattedname('+CONVERT(VARCHAR,@CompId)+','+ CONVERT(VARCHAR,@CompId)+',0) AS Name '
      + ',a.Line1'
      + ',a.Line2'
      + ',a.Line3'
      + ',a.City'
      + ',a.PostalCode'
      +','+ @MultilingualStateName +
      + ',p.CompressedPhone'
      +' FROM pl.users u'+
      +' LEFT JOIN pl.Phones p ON u.UserId = p.UserId '
      +'LEFT JOIN pl.Addresses a ON u.UserId = a.UserId'
      +' INNER Join dbo.States s ON s.stateid = a.stateid'
      + ' WHERE u.UserId = '+CONVERT(VARCHAR,@CompId)
      + ' AND p.phonetypeId = 1'
      + ' AND p.DisplayOrder = 1'
      + ' FOR JSON PATH, INCLUDE_NULL_VALUES ),''[]'') AS Result' 
      
      EXEC(@Address) 
      
  END TRY
  BEGIN CATCH
      THROW;
  END CATCH
  END
  </code></pre>
  <p>I'm calling this with this code:</p>
  <pre><code>DECLARE @Address VARCHAR(MAX)
  
  EXEC [PL].[spGetCompanyAddress] 10606, 'FR', @Address
  
  SELECT @Address AS Address
  </code></pre>
  <p>Output is empty.</p>
  <p>I have also have tried with this code, but still get the same output</p>
  <pre><code>EXEC sp_executesql @Query,
                     N'@Address VARCHAR(MAX) OUTPUT',
                     @Address OUTPUT;
  
  SELECT @Address AddressValue
  </code></pre>
  <p>I want to use the output JSON into another stored procedure's OUTPUT</p>