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