On this page
Why it happens
Error 201 means the procedure or function you called declares a parameter that has no default value, and you did not pass anything for it. When a parameter is declared without a default, SQL Server treats it as required and refuses to run the call until you supply a value. The message names the exact parameter it is missing, in this case @CustomerId.
The Line 0 in the message is normal here: the check happens before the body of the procedure runs, so there is no statement line to point at. The severity is 16, which is an ordinary, catchable request error, not a server fault. See the SQL syntax guide for how parameters are declared and passed.
Common triggers are forgetting an argument in the call, a typo in a named parameter so the name does not match the definition, or application code that skipped a mapping and passed nothing. Note that supplying an explicit NULL is different from supplying nothing: NULL counts as a value, while omitting the parameter entirely is what raises 201.
Examples
Calling a procedure without a required parameter
CREATE PROCEDURE dbo.GetCustomerOrders
@CustomerId INT
AS
SELECT * FROM dbo.Orders WHERE CustomerId = @CustomerId;
GO
-- No value supplied for @CustomerId
EXEC dbo.GetCustomerOrders;Msg 201, Level 16, State 4, Procedure GetCustomerOrders, Line 0 Procedure or function 'GetCustomerOrders' expects parameter '@CustomerId', which was not supplied.
A typo in a named parameter
-- The definition uses @CustomerId, but the caller misspells it.
-- @CustomerID (wrong name) is not recognized, so @CustomerId stays unsupplied.
EXEC dbo.GetCustomerOrders @CustomerIdentifier = 5;Msg 201, Level 16, State 4, Procedure GetCustomerOrders, Line 0 Procedure or function 'GetCustomerOrders' expects parameter '@CustomerId', which was not supplied.
How to fix it
Pass every required parameter, by name
Supply a value for each parameter that has no default. Passing by name makes the call self documenting and avoids relying on positional order.
EXEC dbo.GetCustomerOrders @CustomerId = 5;Give the parameter a default so it becomes optional
If callers should be able to omit the argument, declare a default value in the procedure definition. A parameter with a default may be left out of the call.
CREATE OR ALTER PROCEDURE dbo.GetCustomerOrders
@CustomerId INT = NULL
AS
SELECT * FROM dbo.Orders
WHERE @CustomerId IS NULL OR CustomerId = @CustomerId;
GO
-- Now this runs without error
EXEC dbo.GetCustomerOrders;Check the parameter names match exactly
A named argument only binds if its name matches the definition. Compare the caller against the declared name and fix any typo so the value actually reaches the parameter.
-- Definition declares @CustomerId; use that exact name.
EXEC dbo.GetCustomerOrders @CustomerId = 5;Populate the command parameters in application code
When the error comes from an application, make sure the command parameters collection actually contains the required parameter before executing. A skipped mapping sends the call with nothing bound.
-- Pseudocode for the data layer
-- cmd.CommandType = StoredProcedure
-- cmd.CommandText = 'dbo.GetCustomerOrders'
-- cmd.Parameters.Add('@CustomerId', SqlDbType.Int).Value = customerId;
-- Verify @CustomerId is added before cmd.ExecuteReader();
EXEC dbo.GetCustomerOrders @CustomerId = 5;How to prevent it
Always call procedures with named parameters (@CustomerId = 5) rather than by position. Named arguments make missing or misspelled parameters obvious and stop a reordering of the definition from silently breaking callers.
Give optional parameters a default value in the definition, and validate the full parameter list in application code before executing the command. For a refresher on declaring and passing parameters, see the SQL syntax guide and the SQL tutorial.
Common questions
How do I make a stored procedure parameter optional?
Give the parameter a default value in the procedure definition, for example @CustomerId INT = NULL. A parameter that has a default may be omitted from the call, so SQL Server no longer raises error 201 when it is left out. Handle the default inside the body, such as WHERE @CustomerId IS NULL OR CustomerId = @CustomerId.
Why does passing parameters by name help avoid error 201?
Named parameters bind each value to a specific declared name, so a typo or a missing argument shows up immediately instead of shifting values into the wrong slots. It also means you do not depend on the order the parameters were declared, which keeps calls correct if the procedure is later edited.
Is passing NULL the same as not supplying a parameter?
No. Passing NULL supplies a value (NULL) and satisfies the requirement, so it does not raise error 201. Omitting the parameter entirely supplies nothing, and only that triggers the error when the parameter has no default.