Procedure or function !!! has too many arguments specified

Question

I am developing my very first stored procedure in SQL Server 2008 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!

(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

Solution 1

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.

Solution 2

Use the following command before defining them:

cmd.Parameters.Clear()

Solution 3

This answer is based on the title and not the specific case in the original post.

I had an insert procedure that kept throwing this annoying error, and even though the error says, "procedure....has too many arguments specified," the fact is that the procedure did NOT have enough arguments.

The table had an incremental id column, and since it is incremental, I did not bother to add it as a variable/argument to the proc, but it turned out that it is needed, so I added it as @Id and viola like they say...it works.

Solution 4

For those who might have the same problem as me, I got this error when the DB I was using was actually master, and not the DB I should have been using.

Just put use [DBName] on the top of your script, or manually change the DB in use in the SQL Server Management Studio GUI.

Solution 5

Yet another cause of this error is when you are calling the stored procedure from code, and the parameter type in code does not match the type on the stored procedure.

View more solutions

Comments

[user2006697]

Excellent! This works after deleting @Description NVARCHAR(50) from the declare section. Thank you for responding so fast and clear!

[Ali Adlavaran]

@Darren Is there a mechanism to ignore unexpected parameters?

[CiucaS]

@AliAdlavaran add them a default value

[CarloC]

I had the same. Added the @ID with a default = 0 to the procedure but the VBA object isn't using it in its call nor is it being used in the INSERT but yeah, it works fine now. Funnily enough, when I ran the EXEC in SQL it was fine, but not when called from my VBA project. Has to be a bug!

[Richardissimo]

If I may suggest some improvements to this code... SqlCommand is IDisposable so should be within a using block. You may want to read can we stop using AddWithValue. This isn't thread-safe since the myConnection variable isn't owned by this method, so I hope it's being disposed correctly (since it's also IDisposable). If it was, then you could put that in a using block as well which removes the need for the finally block. Also...

[Richardissimo]

… The try...catch...rethrow-as-Exception means that the calling code will be unable to determine what kind of problem occurred, and all the details of those exceptions will have been lost, since the only information that is being passed on is the Message part of the exception. This design is opening and closing the connection within the loop, which won't be too bad, due to connection pooling; but could be avoided by opening and closing the connection outside the loop. There is no need to clear the parameters, since the cmd was only created two lines earlier. And...

[Richardissimo]

...finally, rather than a for loop, consider using a foreach; because it makes the code easier to read, and it will be faster because it won't have to do three indexer lookups each time through the loop: listMyClass[i].