title: Calling a stored procedure from another always returns data conversion error in SQL Server
body: <p>There might be number of questions regarding calling stored procedure from another stored procedure in SQL Server, or passing output value from stored procedure to another. But, I have a problem calling a stored procedure having output value, it always return error converting <code>varchar</code> to <code>int</code>.</p>
  
  <p>Stored procedure #1:</p>
  
  <pre><code>CREATE PROCEDURE [dbo].[SP1]
      @VarIn VARCHAR(MAX),
      @VarOut VARCHAR(MAX) OUTPUT
  AS
  BEGIN
      SET @VarOut = '20, 21'
  END
  </code></pre>
  
  <p>Stored procedure #2</p>
  
  <pre><code>CREATE PROCEDURE [dbo].[SP2]
      @Var1 VARCHAR(MAX)
  AS
  BEGIN
      DECLARE @InstNo VARCHAR(MAX)
  
      EXEC [dbo].[SP1] NULL, @InstNo OUTPUT
  
      SELECT @InstNo
  END
  </code></pre>
  
  <p>I'm using right click on the stored procedure to execute it:</p>
  
  <pre><code>DECLARE @return_value int
  
  EXEC    @return_value = [dbo].[SP2]
          @Var1 = NULL
  
  SELECT  'Return Value' = @return_value
  GO
  </code></pre>
  
  <p>The error message is </p>
  
  <blockquote>
    <p>Conversion failed when converting the varchar value '20, 21' to data type int</p>
  </blockquote>
  
  <p>By the way, I got reference from the accepted answer from this <a href="https://stackoverflow.com/questions/2245691/how-to-assign-an-exec-result-to-a-sql-variable">question</a>.</p>