Stored Procedure To Table Variable Issue

Question

Okay, what I'm trying to do is take the results from a stored procedure and put them into a temp table...
Looks like this:

DECLARE @Table TABLE(
 [ID] int,
 [CostA] real,
 [CostB] real,
 [CostC] real
)

INSERT INTO @Table
EXECUTE [dbo].[CostProcedure]
        @RootId = 123
        @Rate = 20

--THEN:
SELECT * FROM @Table     -- Gives Me:

ID     CostA    CostB    CostC
10     0        0        0

-- HOWEVER
EXECUTE [dbo].[CostProcedure]
        @RootId = 123
        @Rate = 20

--GIVES ME  
ID     CostA    CostB    CostC
10     1.0987   0.9837   0.65463

So my question is, since the stored procedure is obviously returning the proper results, why when I insert them into a table variable does it only insert the first column of the results and give all zeros on the subsequent columns? Is there some known issue with this I don't know about??? I even tried:

INSERT INTO @Table([ID],[CostA],[CostB],[CostC])
EXECUTE [dbo].[CostProcedure]
            @RootId = 123
            @Rate = 20

It yielded the same result...
I even tried a throwback, and tried a temp table (Create table #whatever)... Still the same result... What the heck am I missing?

I'm just wondering... If CostProcedure has a StoredProcedure inside of it that writes a value to a temp table, the same as above, maybe there is some limit on the number of levels you can nest stored procedures writing to temp tables?

Answer 1

There may be a strange data conversion issue there. Does the stored procedure return REALs like your table has defined, or are they NUMERICs or soemthing else?

Make sure the datatypes are consistant.

you can do some testing within the stored procedure using something like this:

SELECT SQL_VARIANT_PROPERTY(cast(12345.6789 AS decimal(9,4))+cast(5.678912 AS decimal(9,6)), 'BaseType')
SELECT SQL_VARIANT_PROPERTY(cast(12345.6789 AS decimal(9,4))+cast(5.678912 AS decimal(9,6)), 'Precision')
SELECT SQL_VARIANT_PROPERTY(cast(12345.6789 AS decimal(9,4))+cast(5.678912 AS decimal(9,6)), 'Scale')

to show you what exactly is being returned (replace the "cast(12345.6789 AS decimal(9,4)" with your returned column value). Just output these values from the stored procedure and make your table's columns match, then give that a try.

Answer 2

I found a workaround, I changed the required stored proc to return scalar outputs rather than a dataset... Not what I wanted, but means to an end!