程序或功能！指定的参数过多 [英] Procedure or function !!! has too many arguments specified
问题描述
我正在开发 SQL Server 2008 R2 中的第一个存储过程，需要有关错误消息的建议。
< blockquote>
过程或函数xxx指定的参数过多
c $ c> [dbo]。[M_UPDATES] 调用另一个名为 etl_M_Update_Promo 的存储过程。
当通过鼠标右键单击和执行存储过程调用查询中出现的查询时调用 [dbo]。[M_UPDATES] -window is：
USE [Database_Test]
GO
DECLARE @return_value int
EXEC @return_value = [dbo]。[M_UPDATES]
SELECT'返回值'= @return_value
GO
输出为
Msg 8144，级别16，状态2，过程etl_M_Update_Promo，行0
过程或函数etl_M_Update_Promo指定的参数过多。
QUESTION ：此错误消息是什么意思，即哪里有太多参数？如何识别它们？
我发现几个线程询问此错误消息，但提供的代码都与我不同（如果不是其他语言如 C＃反正）。所以没有答案解决了我的 SQL 查询（即SP）的问题。
注意：该代码用于两个SP，但我更改了数据库名称，表名和列名称。所以，请不要担心命名约定，这些只是示例名称！
先感谢任何建议和想法！
（1）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）
设置@GenID = SCOPE_IDENTITY（）
设置@Description ='M更新'
BEGIN
EXEC etl.etl_M_Update_Promo @GenID，@Description
END
GO
（2）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
，@更新int = 0
，@Deleted int = 0
，@OwnGenId位= 0
开始尝试
如果@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
使用OPENQUERY（M，'select * from m.PROMO'）as SOURCE
ON（TARGET。[E] = SOURCE。[E]）
当匹配和目标[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]
当目标不成功时
INSERT（[E]
，[A]
，[B]
，[C]
，[D]
，[F]
，[G]
，[H]
，[I] ，[J]
，[K]
，[L]
）
VALUES（SOURCE。[E]
，SOURCE。 ，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
更改，
COUNT（*）AS CountPerChange
FROM @SummeryOfTable
GROUP BY更改
）
SELECT
@Inserts =
CASE更改
WHEN'INSERT'THEN CountPerChange ELSE @Inserts
END，
@Updates =
CASE更改
WHEN'UPDATE'THEN CountPerChange ELSE @Updates
END，
@Deleted =
CASE更改
WHEN'DELETE' THEN CountPerChange ELSE @Deleted
END
FROM cte
INSERT INTO Logging.dbo.ETL_log（GenID，Startdate，Enddate，Process，Message，Inserts，Updates，已删除，说明）
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，进程，消息，错误无，描述）
值（@GenId，@start，GETDATE（），@Process，@ErrorMsg，@ ErrorNo，@ Description）
GO
参数（@GenId和@Description）：
EXEC etl.etl_M_Update_Promo @GenID，@Description
但是，您已声明函数接受1个参数：
ALTER PROCEDURE [etl]。[etl_M_Update_Promo]
@GenId bigint = 0
SQL Server告诉您 [etl_M_Update_Promo] 只有1个参数（ @GenId ）
您可以通过指定 @Description 来更改过程以接受两个参数。
ALTER PROCEDURE [etl]。[etl_M_Update_Promo]
@GenId bigint = 0，
@Description NVARCHAR（50）
AS
....其余的代码。
I am developing my very first stored procedure in SQL Server 2008 R2 and need advice concerning the errors message.
Procedure or function xxx too many arguments specified
which I get after executing the stored procedure [dbo].[M_UPDATES] that calls another stored procedure called etl_M_Update_Promo.
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
The output is
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?
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).
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!
Thanks in advance for any advice and thoughts!
(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) 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
You invoke the function with 2 parameters (@GenId and @Description):
EXEC etl.etl_M_Update_Promo @GenID, @Description
However you have declared the function to take 1 argument:
ALTER PROCEDURE [etl].[etl_M_Update_Promo]
@GenId bigint = 0
SQL Server is telling you that [etl_M_Update_Promo] only takes 1 parameter (@GenId)
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屋！
