SQL Server Error Messages - Msg 201¶

Error Message¶

Server: Msg 201, Level 16, State 4, Line 1
Procedure or function '<Procedure Name>' expects parameter '<Parameter Name>', which was not supplied.

Causes¶

As the message suggests, this error occurs when executing a stored procedure or a user-defined function that expects a parameter that was not supplied and the parameter does not have a default value specified during creation. If the parameter has a default value assigned, this error message will not occur for that particular parameter

To illustrate, the following call to the sp_changedbowner system stored procedure will generate the error if no parameter is passed:

EXECUTE [dbo].[sp_changedbowner]

Msg 201, Level 16, State 4, Procedure sp_changedbowner, Line 0
Procedure or function 'sp_changedbowner' expects parameter '@loginame', which was not supplied.

The sp_changedbowner system stored procedure changes the owner of the current database. The syntax of sp_changedbowner is as follows:

EXECUTE [dbo].[sp_changedbowner] [ @loginname = ] 'login' [, [ @map = ] remap_alias_flag ]

The @loginame parameter is the login ID of the new owner of the current database and is required when calling this system stored procedure. The optional @map parameter, which accepts a value of true or false, indicates whether existing aliases assigned to the old database owner (dbo) are mapped to the new owner of the current database or dropped.

Another way of getting this error using the same sp_changedbowner system stored procedure is using the following script:

EXECUTE [dbo].[sp_changedbowner] @loginname = 'sql2008'

Msg 201, Level 16, State 4, Procedure sp_changedbowner, Line 0
Procedure or function 'sp_changedbowner' expects parameter '@loginame', which was not supplied.

Although the login ID is passed to the stored procedure, the name of the parameter used when executing the stored procedure in this particular script was “@loginname” (with 2 n’s) instead of “@loginame” (with just 1 n).

Solution / Work Around¶

The simplest way to avoid this error is to make sure that all parameters expected by a stored procedure or function that don’t have any default values are assigned a value and that the name of the parameter, if specified, matches the name of the parameter as defined in the stored procedure or function.

To know what parameters are expected by a stored procedure or function, the sp_help system stored procedure can be used, passing the name of the stored procedure or function as a parameter. The sp_help system stored procedure reports information about a database object, a user-defined data type or a data type.

EXECUTE [dbo].[sp_help] 'sp_changedbowner'

Name               Owner   Type               Created_datetime
------------------ ------- ------------------ ------------------
sp_changedbowner   sys     stored procedure   2011-02-25 17:05:47.043

Parameter_name  Type     Length  Prec  Scale  Param_order  Collation
--------------- -------- ------- ----- ------ ------------ ----------
@loginame       sysname  256     128   NULL   1            SQL_Latin1_General_CP1_CI_AS 
@map            varchar  5       5     NULL   2            SQL_Latin1_General_CP1_CI_AS

In the example shown above of executing the sp_changedbowner system stored procedure, by specifying the required parameters with the correct parameter name, this error message will be avoided.

EXECUTE [dbo].[sp_changedbowner] @loginame = 'sql2008'

There are 2 ways of passing parameters to a stored procedure. The first method is using the “@parametername=value” convention wherein the name of the parameter is passed together with the value. Here’s a sample using the sp_changeobjectowner system stored procedure:

EXECUTE [dbo].[sp_changeobjectowner] @objname = 'Customer', @newowner = 'sql2008'

The second method is by simply passing the parameter value without the parameter name. Using the same sp_changeobjectowner, here’s how the call to the sp_changeobjectowner will look like using this method:

EXECUTE [dbo].[sp_changeobjectowner] 'Customer', 'sql2008'

Using the second method, you must pass the parameter values in the same order as the parameters as specified in the stored procedure. In the first method, the order of the parameters need not match the order of the parameters list of the stored procedure.

The preferred method between these two is the first method where the parameter value is passed together with the parameter name. Using the “@parametername = value” convention, even if new parameters with default values are added in a stored procedure somewhere in the parameter list not necessarily at the end, the call to the stored procedure will still work and generate the expected result. Also, the list of parameters in the stored procedure definition can also be rearranged without affecting any calls to it.