程序或函数！！！指定的参数过多 [英] Procedure or function !!! has too many arguments specified

问题描述

我正在 SQL Server 2008 中开发我的第一个存储过程，需要有关错误消息的建议.

I am developing my very first stored procedure in SQL Server 2008 and need advice concerning the errors message.

过程或函数 xxx 指定的参数过多

Procedure or function xxx too many arguments specified

我在执行存储过程 [dbo].[M_UPDATES] 后得到它，该过程调用另一个名为 etl_M_Update_Promo 的存储过程.

which I get after executing the stored procedure [dbo].[M_UPDATES] that calls another stored procedure called etl_M_Update_Promo.

当通过鼠标右键单击和执行存储过程"调用 [dbo].[M_UPDATES](代码见下文)时，查询窗口中出现的查询是:

When calling [dbo].[M_UPDATES] (code see below) via right-mouse-click and ‘Execute stored procedure’ the query that appears in the query-window is:

USE [Database_Test]
GO

DECLARE @return_value int

EXEC    @return_value = [dbo].[M_UPDATES]

SELECT  'Return Value' = @return_value

GO

输出为

消息 8144，级别 16，状态 2，过程 etl_M_Update_Promo，第 0 行
过程或函数 etl_M_Update_Promo 指定的参数过多.

Msg 8144, Level 16, State 2, Procedure etl_M_Update_Promo, Line 0
Procedure or function etl_M_Update_Promo has too many arguments specified.

问题:此错误消息的确切含义是什么，即哪里有太多参数?如何识别它们?

QUESTION: What does this error message exactly mean, i.e. where are too many arguments? How to identify them?

我发现有几个线程询问此错误消息，但提供的代码都与我的不同(如果不是使用另一种语言，例如 C#).所以没有一个答案解决了我的 SQL 查询(即 SP)的问题.

I found several threads asking about this error message, but the codes provided were all different to mine (if not in another language like C# anyway). So none of the answers solved the problem of my SQL query (i.e. SPs).

注意:下面我提供了两个 SP 使用的代码，但我更改了数据库名称、表名称和列名称.所以，请不要担心命名约定，这些只是示例名称！

Note: below I provide the code used for the two SPs, but I changed the database names, table names and column names. So, please, don’t be concerned about naming conventions, these are only example names!

(1) SP1 代码 [dbo].[M_UPDATES]

(1) Code for SP1 [dbo].[M_UPDATES]

USE [Database_Test]
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER PROCEDURE [dbo].[ M_UPDATES] AS
declare @GenID bigint
declare @Description nvarchar(50)

Set @GenID = SCOPE_IDENTITY()
Set @Description = 'M Update'

BEGIN
EXEC etl.etl_M_Update_Promo @GenID, @Description
END

GO

(2) SP2 代码 [etl_M_Update_Promo]

(2) Code for SP2 [etl_M_Update_Promo]

USE [Database_Test]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER PROCEDURE [etl].[etl_M_Update_Promo]
@GenId bigint = 0
as

declare @start datetime = getdate ()
declare @Process varchar (100) = 'Update_Promo'
declare @SummeryOfTable TABLE (Change varchar (20))
declare @Description nvarchar(50)
declare @ErrorNo int
, @ErrorMsg varchar (max)
declare @Inserts int = 0
, @Updates int = 0
, @Deleted int = 0
, @OwnGenId bit = 0

begin try


if @GenId = 0 begin
INSERT INTO Logging.dbo.ETL_Gen (Starttime)
VALUES (@start)

SET @GenId = SCOPE_IDENTITY()
SET @OwnGenId = 1
end


MERGE [Database_Test].[dbo].[Promo] AS TARGET
USING OPENQUERY( M ,'select * from m.PROMO' ) AS SOURCE 
ON (TARGET.[E] = SOURCE.[E]) 


WHEN MATCHED AND  TARGET.[A] <> SOURCE.[A]
  OR TARGET.[B] <> SOURCE.[B]
  OR TARGET.[C] <> SOURCE.[C]
  THEN 
UPDATE SET TARGET.[A] = SOURCE.[A]
  ,TARGET.[B] = SOURCE.[B]
  , TARGET.[C] = SOURCE.[c]

WHEN NOT MATCHED BY TARGET THEN 
INSERT ([E]
  ,[A]
  ,[B]
  ,[C]
  ,[D]
  ,[F]
  ,[G]
  ,[H]
  ,[I]
  ,[J]
  ,[K]
  ,[L]  
  ) 
VALUES (SOURCE.[E]
  ,SOURCE.[A]
  ,SOURCE.[B]
  ,SOURCE.[C]
  ,SOURCE.[D]
  ,SOURCE.[F]
  ,SOURCE.[G]
  ,SOURCE.[H]
  ,SOURCE.[I]
  ,SOURCE.[J]
  ,SOURCE.[K]
  ,SOURCE.[L]
)

OUTPUT $ACTION  INTO @SummeryOfTable; 


with cte as (
SELECT
Change,
COUNT(*) AS CountPerChange
FROM @SummeryOfTable
GROUP BY Change
)

SELECT
@Inserts =
    CASE Change
        WHEN 'INSERT' THEN CountPerChange ELSE @Inserts
    END,
@Updates =
    CASE Change
        WHEN 'UPDATE' THEN CountPerChange ELSE @Updates
    END,
@Deleted =
    CASE Change
        WHEN 'DELETE' THEN CountPerChange ELSE @Deleted
    END
FROM cte


INSERT INTO Logging.dbo.ETL_log (GenID, Startdate, Enddate, Process, Message, Inserts, Updates, Deleted,Description)
VALUES (@GenId, @start, GETDATE(), @Process, 'ETL succeded', @Inserts, @Updates,     @Deleted,@Description)


if @OwnGenId = 1
UPDATE Logging.dbo.ETL_Gen
SET Endtime = GETDATE()
WHERE ID = @GenId

end try
begin catch

SET @ErrorNo = ERROR_NUMBER()
SET @ErrorMsg = ERROR_MESSAGE()

INSERT INTO Logging.dbo.ETL_Log (GenId, Startdate, Enddate, Process, Message, ErrorNo, Description)
VALUES (@GenId, @start, GETDATE(), @Process, @ErrorMsg, @ErrorNo,@Description)


end catch
GO

推荐答案

您使用 2 个参数(@GenId 和 @Description)调用函数:

You invoke the function with 2 parameters (@GenId and @Description):

EXEC etl.etl_M_Update_Promo @GenID, @Description

但是你已经声明函数接受 1 个参数:

However you have declared the function to take 1 argument:

ALTER PROCEDURE [etl].[etl_M_Update_Promo]
    @GenId bigint = 0

SQL Server 告诉你 [etl_M_Update_Promo] 只需要 1 个参数 (@GenId)

SQL Server is telling you that [etl_M_Update_Promo] only takes 1 parameter (@GenId)

您可以通过指定@Description 来更改过程以采用两个参数.

You can alter the procedure to take two parameters by specifying @Description.

ALTER PROCEDURE [etl].[etl_M_Update_Promo]
    @GenId bigint = 0,
    @Description NVARCHAR(50)
AS 

.... Rest of your code.

这篇关于程序或函数！！！指定的参数过多的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！