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
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
nullin ADO.NET instead ofDBNull.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
-
Pass
DBNull.Valuefor NULLs in ADO.NET, or let the library do it (for example Dapper convertsnullfor you). -
Make sure every
@parameterin the SQL text has a value, with matching names. -
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
208Invalid object name207Invalid column name4104Multi-part identifier not bound209Ambiguous column name2812Could not find stored procedure2714There is already an object named3701Cannot drop, object not found3702Cannot drop database, currently in use
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.
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.