title: How to add a Column to stored procedure result?
body: <p>I have the following sql procedure:</p>
  
  <pre><code>DECLARE @temp table
      (
         Column1 nvarchar(1000)
      )
  
      INSERT @temp (Column1)
      SELECT  fld_4
      FROM    MyTable
      WHERE   fld_1 = @param1 and
              fld_2 = @param2 and
              fld_3 = @param3
  
       SELECT ROW_NUMBER() OVER(ORDER BY Column1 ASC) AS Number, Split.a.value('.', 'VARCHAR(100)') AS Result
       FROM  
       (
           SELECT Column1,  
               CAST ('&lt;M&gt;' + REPLACE(Column1, ';', '&lt;/M&gt;&lt;M&gt;') + '&lt;/M&gt;' AS XML) AS Result  
           FROM  @temp
       ) AS A CROSS APPLY Result.nodes ('/M') AS Split(a);
  </code></pre>
  
  <p>Structure of MyTable:</p>
  
  <pre><code>| fld_1 | fld_2 | fld_3 | fld_4 | ... | fld_9 | ...|
  </code></pre>
  
  <p>Where fld_4 contains something like this:</p>
  
  <pre><code>-9;-9;-1;-9;-9;-9;-9;-9;-1;-9;-9;-9;-9;-9;-9;-9;-9;-9;-1;-9;-9;-9;-9;-9;-9;-9;-9;-9;-1;-9;-1;-9;-9;-9;-1;-9;-9;-9;-9;-9;-9;-1;-1;-1;-1;-9;-1;-1;-9;-9;-9;-9;-1;-9;-1;-9;-9;-9;-1;-9;-1;-9;-1;-9;-9;-9;-9;-1;-9;-9;-1;-1;-9;-1;-1;0000;FFF8;-9;-9;-9;-1;-9;-1;-9;FFF6;-9;-1;-9;-1;-9;-1;-9;-9;-9;-9;-9;-9;-9;-9;-9;-9;-9;-9;-9;-9;-9;-9;-9;-9;-9;-9;-9;-9;-9;-9
  </code></pre>
  
  <p>My procedure returns a table with this structure:</p>
  
  <pre><code>| Number | Result |
  |   1    |   -9   |
  |   2    |   -9   |
  |   3    |   -1   |
  |  ...   |   ...  |
  </code></pre>
  
  <p>Now, I want to achieve the following result:</p>
  
  <pre><code>| Number | Result |     NewColumn    |
  |   1    |   -9   |  Value of fld_9  |
  |   2    |   -9   |  Value of fld_9  |
  |   3    |   -1   |  Value of fld_9  |
  |  ...   |   ...  |  Value of fld_9  |
  </code></pre>
  
  <p>Any suggestions? </p>