On this page
Why it happens
Error 8114 means SQL Server tried to turn a value of one type into another and the value did not fit the target type. The classic case is a varchar that holds text like abc, a stray letter, or a thousands separator being converted to numeric or decimal. Because a number cannot represent those characters, the whole statement stops with severity 16.
Unlike error 245, which usually points at a single visible literal, 8114 often surfaces through a stored procedure parameter or an implicit conversion buried in a join or WHERE clause. The message names the two types but not the offending row, so the failing value can be harder to trace. The type in the message is your first clue about where to look.
Common triggers are passing a non-numeric string to a numeric parameter, converting a varchar that contains letters, symbols, commas, or a currency sign to a numeric type, and an implicit conversion where SQL Server compares or assigns a string column to a numeric one inside a procedure.
Examples
A numeric parameter receiving non-numeric text
CREATE PROCEDURE dbo.SetPrice
@price NUMERIC(10,2)
AS
SELECT @price AS price;
GO
-- The application sends 'abc' where a number is expected
EXEC dbo.SetPrice @price = 'abc';Msg 8114, Level 16, State 5, Procedure SetPrice, Line 0 Error converting data type varchar to numeric.
Converting a string with a thousands separator
-- The comma is not valid inside a numeric literal
SELECT CAST('1,234.56' AS NUMERIC(10,2)) AS amount;Msg 8114, Level 16, State 5, Line 2 Error converting data type varchar to numeric.
An implicit conversion inside a query
-- code is varchar and holds values like 'A100'.
-- Comparing it to an integer forces an implicit convert.
SELECT * FROM dbo.Products WHERE code = 100;Msg 8114, Level 16, State 5, Line 3 Error converting data type varchar to numeric.
How to fix it
Use TRY_CONVERT or TRY_CAST to isolate the bad rows
Instead of failing the whole statement, TRY_CONVERT (and TRY_CAST) return NULL when a value cannot convert. That lets you keep going and then find exactly which values are the problem.
-- Rows where the string is not a valid number come back as NULL
SELECT id, raw_amount, TRY_CONVERT(NUMERIC(10,2), raw_amount) AS amount
FROM dbo.Imports;
-- List only the offending values
SELECT id, raw_amount
FROM dbo.Imports
WHERE TRY_CONVERT(NUMERIC(10,2), raw_amount) IS NULL
AND raw_amount IS NOT NULL;Clean the string before converting
Strip commas, currency symbols, and spaces so only digits and a decimal point remain, then convert. This handles formatted values such as $1,234.56.
SELECT CAST(
REPLACE(REPLACE(REPLACE('$1,234.56', '$', ''), ',', ''), ' ', '')
AS NUMERIC(10,2)) AS amount;Send the correct type from the application
When the error comes from a procedure parameter, the real fix is to pass a numeric value, not a string. Type the parameter in the calling code so a number is bound, or convert it once at the boundary before the call.
-- Convert to the parameter type before executing
DECLARE @price NUMERIC(10,2) = TRY_CONVERT(NUMERIC(10,2), '19.99');
IF @price IS NULL
THROW 50000, 'Price is not a valid number', 1;
EXEC dbo.SetPrice @price = @price;Find the value that fails to convert
Use a query that flags every row whose text is not a clean number. CAST would stop at the first bad row, so use TRY_CONVERT to scan them all at once.
SELECT id, raw_amount
FROM dbo.Imports
WHERE raw_amount IS NOT NULL
AND TRY_CONVERT(DECIMAL(18,4), raw_amount) IS NULL;How to prevent it
Validate and type inputs at the boundary. Convert user or file input to the target numeric type once, as data enters the system, and reject anything that fails so a bad value never reaches a query or procedure. TRY_CONVERT and TRY_CAST make that check cheap.
Store numbers in numeric columns rather than varchar. When numeric data lives in a numeric column, there is nothing to convert at read time and implicit conversions in joins and filters disappear. Give procedure parameters the exact type they represent so the caller must supply a compatible value.
Common questions
What is the difference between error 8114 and error 245?
Both are conversion failures, but 245 usually points at a single visible literal in your statement, so the bad value is easy to spot. Error 8114 more often comes from a stored procedure parameter or an implicit conversion inside a query, where the failing value is not written in the SQL you are looking at, which makes it harder to trace.
How do I find which value caused error 8114?
Replace CAST or CONVERT with TRY_CONVERT or TRY_CAST, which return NULL instead of failing. Then run a query that selects the rows where the converted result is NULL but the source is not NULL. Those rows contain the exact strings that cannot convert, such as text, commas, or currency symbols.
How do I fix SQL Server error 8114?
Make sure the value being converted is a valid number for the target type. Clean the string first by stripping commas, currency symbols, and spaces, pass the correct type to procedure parameters from the application, and use TRY_CONVERT to turn bad values into NULL so you can isolate and correct them.