// SQL Server error reference

SQL Server error 8178: Parameterized query expects a parameter

A parameterized query (sp_executesql) declares a parameter that the call didn't supply, usually a NULL value the client library left out.

Error number
8178
Severity
16
Category
Names and objects
Error message
The parameterized query '%.*ls' expects the parameter '%.*ls', which was not supplied.

What it means

Client libraries send parameterized SQL through sp_executesql with a list of declared parameters and their values. If a declared parameter has no value, SQL Server raises 8178. In .NET a classic cause is setting a parameter's value to null, which means "not supplied"; a database NULL must be DBNull.Value.

Common causes

  • A parameter value of null in ADO.NET instead of DBNull.Value.
  • A parameter declared in the SQL text but never added to the command.
  • A mismatch between parameter names in the SQL and those added to the command.

How to fix it

  1. Pass DBNull.Value for NULLs in ADO.NET, or let the library do it (for example Dapper converts null for you).

  2. Make sure every @parameter in the SQL text has a value, with matching names.

  3. In T-SQL, supply every declared parameter.

    EXEC sys.sp_executesql
        N'SELECT * FROM dbo.Orders WHERE CustomerId = @c AND Status = @s',
        N'@c int, @s varchar(20)',
        @c = 42, @s = NULL;

Related errors

More names and objects errors

The code and message text are from the Microsoft Database Engine events and errors reference. Browse the full SQL Server error list or search every database error.

// fewer errors, faster fixes

Work on SQL Server in SQLly

A native SQL client for macOS, Windows and Linux with schema-aware IntelliSense, guardrails for risky statements and clear error messages, for SQL Server and the other databases you use.