存储过程:减少表数据
一个关于存储过程的简单问题。
我有一个存储过程在表中收集一大堆数据。然后,我从另一个存储过程中调用该过程。我可以将数据复制到调用过程中创建的新表中,但据我所知,这些表必须相同。
这是对的吗?或者有没有办法只插入我想要的数据?
例如......
我有一个返回此的过程:
SELECT @batch 作为批处理, @计数为数量, pd.位置, 强制转换(pd.GL 作为十进制(10,3))作为[长度], 将(pd.GW 作为十进制(10,3))转换为宽度, 铸造(pd.GT作为十进制(10,3))作为厚度 来自财产数据 pd 分组依据 pd.位置, pd.GL, GW, pd.GT
然后我调用此过程,但只需要以下数据:
DECLARE @BatchTable TABLE ( 批处理 varchar(50), [长度] 十进制(10,3), 宽度小数(10,3), 厚度小数(10,3), )
INSERT @BatchTable (批次, [长度], 宽度, 厚度) EXEC dbo.batch_drawings_NEW @batch
所以在第二个命令中我不需要数量和位置值。
但是上面的代码不断返回错误:
“插入错误:列名称或提供的值的数量与表不匹配”
A simple question about Stored Procedures.
I have one stored procedure collecting a whole bunch of data in a table. I then call this procedure from within another stored procedure. I can copy the data into a new table created in the calling procedure but as far as I can see the tables have to be identical.
Is this right? Or is there a way to insert only the data I want?
For example....
I have one procedure which returns this:
SELECT
@batch as Batch,
@Count as Qty,
pd.Location,
cast(pd.GL as decimal(10,3)) as [Length],
cast(pd.GW as decimal(10,3)) as Width,
cast(pd.GT as decimal(10,3)) as Thickness
FROM propertydata pd
GROUP BY
pd.Location,
pd.GL,
pd.GW,
pd.GT
I then call this procedure but only want the following data:
DECLARE @BatchTable TABLE (
Batch varchar(50),
[Length] decimal(10,3),
Width decimal(10,3),
Thickness decimal(10,3),
)
INSERT @BatchTable (Batch, [Length], Width, Thickness)
EXEC dbo.batch_drawings_NEW @batch
So in the second command I don't want the Qty and Location values.
However the code above keeps returning the error:
"Insert Error: Column name or number of supplied values does not match table"
如果你对这篇内容有疑问,欢迎到本站社区发帖提问 参与讨论,获取更多帮助,或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱,如果其他用户或者作者回复了您的评论,将不能在第一时间通知您!
发布评论
评论(1)
这些表不必相同,第一个存储过程的查询结果需要与第二个存储过程中定义的列匹配,但事实并非如此。
您需要更改第一个存储过程以不返回数量和位置。
The tables don't have to be identical, the results of the query from the first stored proc need to match the columns defined in the second, and they don't.
You need to change the first stored procedure to not return Qty and Location.