|
|
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
|