在存储过程中运行存储过程 [英] Running Stored Procedure inside stored procedure
问题描述
我必须分开 同一服务器上的数据库。我创建了一个存储过程，以获取顶级部分中没有与之相关的任何费用的项目数。
;
USE [A]
GO
/ ******对象： StoredProcedure [dbo]。[BomPartsNoCost]  脚本日期：2/13/2019 9:39:11 AM ****** /
$
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
- ============================= ================
- 作者： <作者，名称> ;
- 创建日期：<创建日期,,>
- 说明： < Description ,,>
- =================================== ============
ALTER程序[dbo]。[BomPartsNoCost]
- 在此处添加存储过程的参数
@StockCode varchar（30），
@ReturnInt int输出
AS
BEGIN
- 添加SET NOCOUNT ON以防止额外的结果集来自
- 干扰SELECT语句。
SET NOCOUNT ON;
&NBSP; - 在此插入程序声明
使用RPL（PART，SUBPART，QUANTITY）AS
&NBSP; （ SELECT ROOT.ParentPart，ROOT.Component，ROOT.QtyPer
FROM [A]。[dbo]。[BomStructure] ROOT
WHERE ROOT.ParentPart = @StockCode
UNION ALL
; SELECT CHILD.ParentPart，CHILD.Component，CHILD.QtyPer
FROM RPL PARENT，[A]。[dbo]。[BomStructure] CHILD
WHERE PARENT.SUBPART = CHILD.ParentPart
）
选择@ ReturnInt =（选择Count（PART）为'PartsNoCost'
FROM RPL LEFT jOIN A.dbo .InvMaster我是
在RPL.SUBPART = I.StockCode
LEFT jOIN A.dbo.InvMaster IM
on RPL.PART = IM.StockCode
其中I.MaterialCost = 0且SUBPART不在（'3536000'，'1511100'，'2107700'））¥b $ b END
当我尝试在另一个数据库中的另一个存储过程中调用它并运行它时，它只是继续运行并且正在运行。如果我将@ReturnInt从存储过程调用中删除，当我停止运行它时会出现错误，它需要另一个参数
但是当我在下面添加它时，我得到了A.dbo.BomPartsNoCost下的红色波浪线，说过程或函数A.dbo.BomPartsNoCost有多个参数指定。
USE [MF]
GO
/ ******对象： StoredProcedure [dbo]。[GetBomSubPartsNoCost] &NBSP;脚本日期：2/13/2019 9:08:12 AM ****** /
$
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
- ============================= ================
- 作者： Shane McLane
- 创建日期：02/13/2019
- 说明： 计算子部件的数量partent parts bom并将它们每天放入零件表中。
- ============================ =================
$
ALTER PROCEDURE [dbo]。[GetBomSubPartsNoCost]
- 在此处添加存储过程的参数
AS
BEGIN
- 添加SET NOCOUNT ON以防止额外的结果集来自
- 干扰SELECT语句。
SET NOCOUNT ON;
&NBSP; - 在此插入程序声明
声明SysproCursor CURSOR
对于选择PartNumberA，来自MF.dbo.PriceList的NoCostSubParts其中PartNumberA<> ''
打开SysproCursor
声明 @StockCode Char（30），
@NoCostSubParts int
从SysproCursor获取下一个到@StockCode，@ NostostSubParts
而（@@ FETCH_STATUS = 0）
开始¥b $ b < span style ="white-space：pre"> 声明@ReturnInt int
设置@ReturnInt = 0
执行A.dbo.BomPartsNoCost @StockCode，@ ReturnInt = @ReturnInt输出
选择@ReturnInt为N'@ ReturnInt'
更新MF.dbo.PriceList
设置NoCostSubParts = @ NoCostSubParts PartNumberA = @ StockCode
$
结束
从SysproCursor获取下一个到@StockCode，@ NoCostSubParts
关闭SysrpoCusror
Deallocate SysproCursor
结束
红色波浪线来自SSMS中的Intellinonsense，所以请忽略它。或者将其关闭。
以这种方式更改光标：
声明SysproCursor CURSOR STATIC LOCAL
I have to separate Databases on the same server. I created a stored procedure to get the number of items in a top level part that do not have any cost associated with them.
USE [A]
GO
/****** Object: StoredProcedure [dbo].[BomPartsNoCost] Script Date: 2/13/2019 9:39:11 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author: <Author,,Name>
-- Create date: <Create Date,,>
-- Description: <Description,,>
-- =============================================
ALTER PROCEDURE [dbo].[BomPartsNoCost]
-- Add the parameters for the stored procedure here
@StockCode varchar(30),
@ReturnInt int Output
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
-- Insert statements for procedure here
With RPL (PART, SUBPART, QUANTITY) AS
( SELECT ROOT.ParentPart, ROOT.Component, ROOT.QtyPer
FROM [A].[dbo].[BomStructure] ROOT
WHERE ROOT.ParentPart = @StockCode
UNION ALL
SELECT CHILD.ParentPart, CHILD.Component, CHILD.QtyPer
FROM RPL PARENT, [A].[dbo].[BomStructure] CHILD
WHERE PARENT.SUBPART = CHILD.ParentPart
)
Select @ReturnInt=(Select Count(PART) as 'PartsNoCost'
FROM RPL LEFT jOIN A.dbo.InvMaster I
on RPL.SUBPART = I.StockCode
LEFT jOIN A.dbo.InvMaster IM
on RPL.PART = IM.StockCode
Where I.MaterialCost = 0 and SUBPART not in ('3536000', '1511100', '2107700'))
END
No when I try to call this in the other stored procedure in the other database and run it it just keeps running and now working. If I leave @ReturnInt off the stored procedure call I get errors when I stop running it that it needs another argument but when I add it like below I get the red squiggly line under A.dbo.BomPartsNoCost saying Procedure or Function A.dbo.BomPartsNoCost has to many arguments Specified.
USE [MF]GO
/****** Object: StoredProcedure [dbo].[GetBomSubPartsNoCost] Script Date: 2/13/2019 9:08:12 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author: Shane McLane
-- Create date: 02/13/2019
-- Description: Calculates the number of sub parts in the partent parts bom and put them in the parts table daily.
-- =============================================
ALTER PROCEDURE [dbo].[GetBomSubPartsNoCost]
-- Add the parameters for the stored procedure here
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
-- Insert statements for procedure here
Declare SysproCursor CURSOR
For Select PartNumberA ,NoCostSubParts from MF.dbo.PriceList Where PartNumberA <> ''
Open SysproCursor
Declare @StockCode Char(30),
@NoCostSubParts int
Fetch Next From SysproCursor Into @StockCode, @NoCostSubParts
While (@@FETCH_STATUS = 0)
Begin
Declare @ReturnInt int
Set @ReturnInt = 0
EXECUTE A.dbo.BomPartsNoCost @StockCode, @ReturnInt = @ReturnInt Output
Select @ReturnInt as N'@ReturnInt'
Update MF.dbo.PriceList
Set NoCostSubParts = @NoCostSubParts Where PartNumberA = @StockCode
END
Fetch Next From SysproCursor Into @StockCode, @NoCostSubParts
Close SysrpoCusror
Deallocate SysproCursor
End
The red squiggly line is from Intellinonsense in SSMS, so just ignore it. Or turn it off.
Change the cursor this way:
Declare SysproCursor CURSOR STATIC LOCAL
这篇关于在存储过程中运行存储过程的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
