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   lt  gt  SOURCE  A    OR TARGET  B   lt  gt  SOURCE  B    OR TARGET  C   lt  gt  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

User · Accepted Answer

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.

User · Answer

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

User · Answer

In addition to all the answers provided so far  another reason for causing this exception can happen when you are saving data from list to database using ADO Net   Many developers will mistakenly use for loop or foreach and leave the SqlCommand to execute outside the loop  to avoid that make sure that you have like this code sample for example   public static void Save List lt myClass gt  listMyClass                using  var Scope   new System Transactions TransactionScope                          if  listMyClass Count  gt  0                                for  int i   0  i  lt  listMyClass Count  i                                          SqlCommand cmd   new SqlCommand  dbo SP SaveChanges   myConnection                       cmd CommandType   CommandType StoredProcedure                      cmd Parameters Clear                         cmd Parameters AddWithValue   ID   listMyClass i  ID                       cmd Parameters AddWithValue   FirstName   listMyClass i  FirstName                       cmd Parameters AddWithValue   LastName   listMyClass i  LastName                        try                                               myConnection Open                            cmd ExecuteNonQuery                                              catch  SqlException sqe                                                throw new Exception sqe Message                                             catch  Exception ex                                                throw new Exception ex Message                                             finally                                               myConnection Close                                                                      else                               throw new Exception  List is empty                               Scope Complete

User · Answer

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

User · Answer

Use the following command before defining them   cmd Parameters Clear

User · Answer

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

[sql] Procedure or function !!! has too many arguments specified

Examples related to sql

Examples related to database

Examples related to sql-server-2008

Examples related to tsql

Examples related to stored-procedures