CODEWITHSUNDEEP
sqlsql-servert-sql
MikeA
MikeA

Reputation: 95

Why is SQL Server trying to convert my nvarchar(20) datatype to an int?

I'm getting the "conversion" error in a SQL Select query.

The error is:

Msg 248, Level 16, State 1, Line 6
The conversion of the nvarchar value '7000952682' overflowed an int column.

Problem is, there are no int columns in my table!

Here is the table structure:

enter image description here

Here is the query:

enter image description here

If I set the value of @SU to NULL, then it does return all rows as expected. When the value is set to the string value of '7000952682' I get the error.

Why is SQL Server trying to convert the nvarchar value to an int?

Upvotes: 2

Views: 2801

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1269883

Don't use case in the where clause. The logic is more simply and accurately expressed as:

where (@su is null or @su = '' or @su = su)

Upvotes: 1

Tim Biegeleisen
Tim Biegeleisen

Reputation: 521389

All branches of a CASE expression have to have the same type. In this case (no pun intended), it looks like SQL Server is using an integer type and doing an implicit cast of SU to integer. The problem is that the max value for an integer in SQL Server is roughly 2.1 billion, and the example you gave is using the value 7000952682, hence the overflow.

You have two options here. You could make everything varchar:

CASE WHEN @SU IS NULL OR @SU = '' THEN '1' ELSE [SU] END

Or, you could make everything numeric, using a type that won't overflow, e.g.

CASE WHEN @SU IS NULL OR @SU = ''
     THEN CAST(1 AS numeric(20, 6))
     ELSE CAST([SU] AS numeric(20, 6)) END

As a side note, you could write the first part of your CASE expression more succinctly using COALESCE:

CASE WHEN COALESCE(@SU, '') = '' THEN '1' ELSE [SU] END

Upvotes: 6

Related Questions