表变量问题的存储过程

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?

它产生了相同的结果......我甚至尝试了一个回归,并尝试了一个临时表(创建表#whatever)......仍然是相同的结果......我错过了什么?

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?

我只是想知道......如果CostProcedure里面有一个StoredProcedure,它将一个值写入临时表,与上面相同,也许你可以嵌套存储过程写入临时表的级别数有一些限制?

2 个解决方案

#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?

那里可能存在奇怪的数据转换问题。存储过程是否像表中定义的那样返回REAL,或者它们是NUMERIC还是其他?

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.

向您展示返回的确切内容(用您返回的列值替换“cast(12345.6789 AS decimal(9,4)”)。只需从存储过程输出这些值并使表的列匹配,然后尝试一下。

#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!

我找到了一个解决方法,我更改了所需的存储过程以返回标量输出而不是数据集......不是我想要的,而是意味着结束!

#1