SQL Server Error Messages - Msg 8144

Error Message

Server: Msg 8144, Level 16, State 2,
Procedure Stored Procedure or Function Name, Line 0
Procedure or function Stored Procedure or Function Name 
has too many arguments specified.

Causes:

As the message describes, this error is encountered when you are passing arguments or parameters to a function or stored procedure which is more than what the function or stored procedure is expecting.

To illustrate, let?s say you have the following function definition:

CREATE FUNCTION [dbo].[ufn_Concat]( @String1 VARCHAR(10), @String2 VARCHAR(10) )
RETURNS VARCHAR(20)
AS
BEGIN
    RETURN ISNULL(@String1 + ' ', '') + ISNULL(@String2, '')
END

This function expects only 2 arguments, namely @pString1 and @pString2.  To use this function, you do the following:

SELECT [dbo].[ufn_Concat] ( [FirstName], [LastName] ) AS [FullName]
FROM [dbo].[Customers]

The error will be encountered if you pass more than 2 arguments or parameters to the function, as follows:

SELECT [dbo].[ufn_Concat] ( [FirstName], [MiddleName], [LastName] ) AS [FullName]
FROM [dbo].[Customers]

Server: Msg 8144, Level 16, State 2, Line 1
Procedure or function dbo.ufn_Concat has too many arguments specified.

Solution / Work Around:

To avoid this error from happening, always make sure that you pass the same number of arguments that a stored procedure or function is expecting.  To know the parameters expected by a stored procedure, you can use the sp_help system stored procedure and pass the name of the stored procedure as the parameter.

EXECUTE [dbo].[sp_help] 'ufn_Concat'

The sp_help system stored procedure reports information about a database object, a user-defined data type, or a data type.

The output of the sp_help above with 'ufn_Concat' passed as a parameter is as follows:

Name       Owner Type            Created_datetime
ufn_Concat dbo   scalar function 2015-12-29 14:13:02.320

Parameter_name Type    Length Prec Scale Param_order Collation
               varchar 20     20   NULL  0           SQL_Latin1_General_CP1_CI_AS
@String1       varchar 10     10   NULL  1           SQL_Latin1_General_CP1_CI_AS
@String2       varchar 10     10   NULL  2           SQL_Latin1_General_CP1_CI_AS

• Frequently Asked Questions - SQL Server Error Messages
• Frequently Asked Questions - INSERT Statement
• Frequently Asked Questions - SELECT Statement