Q:

How to add a Column to stored procedure result? 

I have the following sql procedure:
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 ('<M>' + REPLACE(Column1, ';', '</M><M>') + '</M>' AS XML) AS Result  
         FROM  @temp
     ) AS A CROSS APPLY Result.nodes ('/M') AS Split(a);

Structure of MyTable:
| fld_1 | fld_2 | fld_3 | fld_4 | ... | fld_9 | ...|

Where fld_4 contains something like this:
-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

My procedure returns a table with this structure:
| Number | Result |
|   1    |   -9   |
|   2    |   -9   |
|   3    |   -1   |
|  ...   |   ...  |

Now, I want to achieve the following result:
| 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  |

Any suggestions?  

A:

You cannot call a Stored Procedure result directly..
However you can make view or  or table-valued user-defined function then get results..
Other way insert Stored Procedure result on TempTable 
INSERT INTO #tempTable EXEC MyStoredProcedure

look at this Quick Sample 
Create PROCEDURE SampleStoreProc    
AS
BEGIN   
    Select 'Burak', 1 
END
GO

Create Table #TempTable
(    
    MyCol Varchar(50),
    ReferanceCol int
)

Insert into #TempTable Exec SampleStoreProc 

go

Create Table TestTable2 
(
    MyCol2 varchar(250),
    RefCol int
);

go

Insert into TestTable2 values ('Yeni', 1);

Select a.MyCol, b.MyCol2 From #TempTable a
left join TestTable2 b on b.RefCol = a.ReferanceCol

