使用存储过程作为源的 SQL 合并语句
我想做这样的事情，但它无法编译。我的存储过程返回一个表。这就是我想要做的 - 也许有人可以指出我做错了什么，因为这无法编译：
MERGE table AS target
USING (EXEC [dbo].[sp_Something] @Rundate = '5/13/2011', @SPID = 56)
AS source (<Columns Returned By Stored Proc Go Here>)
ON TARGET.ID = SOURCE.ID
WHEN MATCHED THEN
UPDATE SET Field = Value...
WHEN NOT MATCHED THEN
INSERT ( Field )
VALUES (Value);
I am looking to do something like this but it doesn't compile. My stored proc returns a table. Here's what I am trying to do - maybe someone can point to what I am doing wrong as this doesn't compile:
MERGE table AS target
USING (EXEC [dbo].[sp_Something] @Rundate = '5/13/2011', @SPID = 56)
AS source (<Columns Returned By Stored Proc Go Here>)
ON TARGET.ID = SOURCE.ID
WHEN MATCHED THEN
UPDATE SET Field = Value...
WHEN NOT MATCHED THEN
INSERT ( Field )
VALUES (Value);
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（3）
不能在需要表的地方使用存储过程。您必须使用表变量、子查询或表值函数。例如（不确定这是否有效，我以前从未使用过 MERGE）：
A stored procedure cannot be used where tables are expected. You must either use a table variable, subquery or a table-valued function. For example (not sure if this is valid, I've never used
MERGEbefore):您只能
INSERT ... EXEC。解决方法是假脱机到 #temp 表或 @table 变量并将其用于 MERGE。You can only to
INSERT ... EXEC. The workaround is to spool into a #temp table or a @table variable and use that for the MERGE.有时，我创建返回存储过程的函数或视图，然后编写存储过程以仅调用视图/函数。这样我就封装了逻辑，能够在连接中使用查询，并利用存储过程功能。
Sometimes I create functions or views that return what an sproc would, and then write the sproc to just call the view/function. That way I encapsulate logic, am able to use the query in joins, and leverage sproc features.