SQL Server 201: expects parameter, which was not supplied

What it means

The procedure declares a parameter without a default value, so every call has to pass it, and this call didn’t. SQL Server refuses before running any of the procedure:

HResult 0xC9, Level 16, State 4
Procedure or function 'add_order' expects parameter '@total', which was not supplied.

That’s how sqlcmd 18 prints it: the error number in hexadecimal (0xC9 is 201) and no line number, since none of the procedure ran. Caught with TRY … CATCH, ERROR_NUMBER() returns 201 and ERROR_LINE() 0. Two related messages:

8178, for a parameterised query sent through sp_executesql, the way drivers such as .NET’s SqlClient send a query with parameters: The parameterized query '(@id int)SELECT …' expects the parameter '@id', which was not supplied.
313, for a user-defined function called with too few arguments: An insufficient number of arguments were supplied for the procedure or function seo_err_mssql.order_total.

Common causes

The parameter was left out, often after someone added a new parameter to the procedure without a default, and old callers kept calling it the old way.
A misspelt name: @totl = 5 doesn’t match @total, so @total is missing. (A name the procedure doesn’t have, passed alongside all the required ones, gives error 8145 instead: @note is not a parameter for procedure add_order.)
A null value from .NET. In SqlClient, a parameter whose Value is null (rather than DBNull.Value) means “use the parameter’s default”. With no default, that’s error 201, or 8178 for a parameterised query. The same happens with @total = DEFAULT in T-SQL.
A stored procedure called as plain text. If the command text is only the procedure’s name and the driver isn’t told it’s a procedure (CommandType.StoredProcedure in .NET), the parameters are declared for a batch that runs the procedure with no arguments, so the first required one is missing.
A scalar function called with too few arguments (313). Unlike a procedure, a function needs every argument in the call, even ones with defaults: write DEFAULT for those.

How to fix it

Look at what the procedure expects

SELECT p.name AS parameter, TYPE_NAME(p.user_type_id) AS type, p.max_length,
       p.is_output, p.has_default_value
FROM sys.parameters AS p
WHERE p.object_id = OBJECT_ID(N'dbo.add_order')
ORDER BY p.parameter_id;

has_default_value is only reliable for CLR procedures; for T-SQL ones it shows 0 even when the procedure has a default, so read the definition (sp_helptext 'dbo.add_order') to be sure.

Pass parameters by name

Named arguments can come in any order and survive changes to the procedure:

EXEC dbo.add_order @customer_id = 1, @total = 30.00;

Once you name one argument, name all the ones after it; mixing gives error 119.

Send NULL as DBNull.Value

In .NET, set parameter.Value = (object?)value ?? DBNull.Value; so a missing value is sent as NULL instead of being dropped. If the procedure then fails with error 515, the value really is required.

Give the parameter a default

If the procedure can work without it, declare a default, so old callers keep working:

CREATE OR ALTER PROCEDURE dbo.add_order
  @customer_id int,
  @total decimal(10, 2) = 0,
  @status varchar(20) = 'new'
AS
  INSERT INTO dbo.orders (customer_id, total, status)
  OUTPUT inserted.id
  VALUES (@customer_id, @total, @status);

Functions: write DEFAULT

SELECT dbo.order_total(1, DEFAULT) AS paid;

Reproduce it

On SQL Server 2022 (16.0.4295.3) with sqlcmd, a procedure add_order @customer_id int, @total decimal(10,2), @status varchar(20) = 'new' and a function order_total (@customer_id int, @status varchar(20) = 'paid') in a scratch schema:

EXEC seo_err_mssql.add_order @customer_id = 1;
EXEC seo_err_mssql.add_order @customer_id = 1, @total = DEFAULT;
SELECT seo_err_mssql.order_total(1);
EXEC sys.sp_executesql N'SELECT name FROM seo_err_mssql.customers WHERE id = @id', N'@id int';

HResult 0xC9, Level 16, State 4
Procedure or function 'add_order' expects parameter '@total', which was not supplied.
HResult 0xC9, Level 16, State 4
Procedure or function 'add_order' expects parameter '@total', which was not supplied.
Msg 313, Level 16, State 2, Server 7732422b7f56, Line 1
An insufficient number of arguments were supplied for the procedure or function seo_err_mssql.order_total.
Msg 8178, Level 16, State 1, Server 7732422b7f56, Line 1
The parameterized query '(@id int)SELECT name FROM seo_err_mssql.customers WHERE id = @id' expects the parameter '@id', which was not supplied.

Caught in TRY … CATCH, the first one showed ERROR_NUMBER() 201, severity 16, state 4, ERROR_PROCEDURE() seo_err_mssql.add_order and line 0. @totl = 5 gave 201 for @total too. Sending the procedure’s name as the text of a parameterised query failed on its first parameter:

EXEC sys.sp_executesql N'seo_err_mssql.add_order',
  N'@customer_id int, @total decimal(10,2)', @customer_id = 1, @total = 5;

HResult 0xC9, Level 16, State 4
Procedure or function 'add_order' expects parameter '@customer_id', which was not supplied.

Passing @total = NULL explicitly got past the check and failed inside the procedure with 515. order_total(1, DEFAULT) returned 30.00, and add_order with both required parameters inserted a row (in a transaction we rolled back). sys.parameters showed has_default_value 0 for @status, which has a default. SQL Server 2019 and 2025 printed the same messages.

In Inlet

Inlet’s sidebar lists each schema’s procedures and functions with their arguments, so you can see which parameters a call needs. Query tabs show every result set and PRINT message a procedure returns. When SQL Server refuses, Inlet shows its message with SQL Server error 201, state 4, severity 16 and links to this page.

Procedure or function expects parameter which was not supplied