none
Running Stored Procedure inside stored procedure RRS feed

  • Question

  • 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


    Wednesday, February 13, 2019 4:16 PM

All replies

  • 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


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

    Wednesday, February 13, 2019 11:01 PM
  • Hi shanemclane,

    As you said that you can execute your script successfully, and I think the red squiggly line  might be related to cache and it will not affect your script . Please refresh local cache (open SSMS--->Edit--->IntelliSense--->Refresh local Cache) and then the red squiggly line will disappear.

     

    Hope it can help you.

     

    Best Regards,

    Rachel


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Thursday, February 14, 2019 7:50 AM