主页 / 问题 / 1018366
Accepted
Polyakov Sergey
Asked:2020-08-29 00:43:40 +0000 UTC2020-08-29 00:43:40 +0000 UTC

如何通过调用具有多个参数的存储过程来获取表

  • 772

有必要从一个 HP 调用另一个并获得一个表作为结果。

我的行动：

declare @resultMoneyDms Table(Closing_Year int, Closing_Month int, mm_Amount int)
                insert into @resultMoneyDms EXEC MoneyDmsInfo @EndDate = '2017-12-14T00:00:00', @Type = 'Billed'

抛出以下错误：

Procedure or function MoneyDmsInfo has too many arguments specified.

但是，可调用 HP 包含并使用所有传递的参数。

称为惠普：

    ALTER PROCEDURE [dbo].[MoneyDmsInfo] @EndDate DateTime, @Type nvarchar(50)
    AS
    BEGIN
      SET NOCOUNT ON;

      IF @Type = 'Billed' 
      begin

      declare @DmsBilled Table (Closing_Year int, Closing_Month int, mm_Amount int)

      insert into @DmsBilled SELECT Closing_Year = DATEPART(YEAR, FO.DocDate), Closing_Month = DATEPART(MONTH, FO.DocDate), sum(FO.Amount) AS mm_Amount
      FROM
          [BillLine] AS B
        INNER JOIN [FinOperation] AS FO ON 
          B.ID = FO.ID
        LEFT JOIN [SaleContract] AS SC ON 
          FO.[AgreementID] = SC.ID
        LEFT JOIN [FinSource] AS a3 ON 
          SC.[FinSourceID] = a3.ID
      WHERE FO.DocDate BETWEEN DATEADD(yy, DATEDIFF(yy, 0, GETDATE()), 0) AND @EndDate and  a3.FinSourceType in (6,7) 
      group by DATEPART(YEAR, FO.DocDate), DATEPART(MONTH, FO.DocDate)
      order by DATEPART(YEAR, FO.DocDate), DATEPART(MONTH, FO.DocDate)
select * from @DmsBilled

  end

……

我尝试通过 MSSQL Studio 自动生成的代码调用此过程，该工作室提供以下内容。编码：

DECLARE @return_value int

EXEC    @return_value = [dbo].[MoneyDmsInfo]
        @EndDate = '2017-12-14T00:00:00',
        @Type = 'Billed'

SELECT  'Return Value' = @return_value

但是在这里，当我尝试替换DECLARE @return_value int为 Table 类型时，会出现错误Must declare the scalar variable "@return_value".

底线是我需要从具有两个或更多参数的过程调用中获取 Table 类型。这里哪里可以出错？

sql

1 个回答

  1. Best Answer

    首先，您给出的程序文本中有错误。至少没有足够的最终“END”！其次，如果您修复程序，您的片段就可以工作（自然，我没有考虑程序中的查询文本，因为这些表不存在）。

    以下代码在 SQL Server 2005 和 SQL Server 2017 上都适用于我。

    IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[TestSP1]') AND type in (N'P', N'PC'))
BEGIN
EXEC dbo.sp_executesql @statement = N'CREATE PROCEDURE [dbo].[TestSP1] AS' 
END
GO
ALTER PROCEDURE [dbo].[TestSP1]
     @EndDate datetime
    ,@Type    nvarchar(50)
AS
BEGIN
    SET NOCOUNT ON;

    IF @Type = 'Billed'
    BEGIN
        DECLARE @DmsBilled TABLE ([Closing_Year]  int
                                 ,[Closing_Month] int
                                 ,[mm_Amount]     int);

        insert into @DmsBilled
        select 1, 1, 1
        union all
        select 2, 2, 2

        select * from @DmsBilled
    END
END
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[TestSP2]') AND type in (N'P', N'PC'))
BEGIN
EXEC dbo.sp_executesql @statement = N'CREATE PROCEDURE [dbo].[TestSP2] AS' 
END
GO
ALTER PROCEDURE [dbo].[TestSP2]
AS
BEGIN
    SET NOCOUNT ON;

    DECLARE @resultMoneyDms TABLE ([Closing_Year]  int
                                  ,[Closing_Month] int
                                  ,[mm_Amount]     int);

    INSERT @resultMoneyDms
      EXEC [dbo].[TestSP1]
           @EndDate = '2017-12-14T00:00:00'
          ,@Type    = 'Billed'

    SELECT *
      FROM @resultMoneyDms
END
GO
EXEC [dbo].[TestSP2]
    • 2

相关问题