SQL Error (245): Conversion failed when converting the nvarchar value '50%' to data type int

Question

Definitions

[Rec_Num] [int] IDENTITY(1,1) NOT NULL,
[Pro_PCT] [nvarchar](50) NULL
[Max_Off] [nvarchar](50) NULL
[IsExceeds]  AS (CONVERT([int],[Pro_PCT])-CONVERT([int],replace([Max_Off],'%','')))

Data

Query

select top 200 * from dbo.FO_Entry
where isexceeds>0
order by Rec_Num desc

The above query works fine and shows the data. But when I change the top 200 to 201 then it throws the below error.

If I run the Top 201 statement 2 or three times then it shows the data. Again if I change the Top 201 to To 202 then again the error popsup.

Any suggestion will be very much helpful.

Note:- Problem occurs when I use where IsExceeds>0 condition. Also I believe that the IsExceeds calculation may be causing this issue.

Answer 1

The real problem is your design, fix that, and the problem goes away. Your column Pro_PCT is clearly an int, and your column Max_off is clearly meant to be a decimal; as it is a percentage.

We'll fix Pro_Pct first. Firstly check if any values are "bad" (which I don't doubt there will be because you're storing numerical data as a nvarchar):

SELECT YourIdColumn
       Pro_PCT
FROM dbo.YourTable
WHERE TRY_CONVERT(int,Pro_PCT) IS NULL
  AND Pro_PCT IS NOT NULL;

That will give you a dataset back of all the bad data. You'll need to fix all of those values.

After that, you can ALTER the table:

ALTER TABLE dbo.YourTable ALTER COLUMN ProPCT int NULL; --Change to NOT NULL as appropriate

Now the percentage. First we need to remove all the percent ('%') characters and turn the value into a decimal. Again, let's get the bad data:

SELECT YourIDColumn,
       [Max_Off]
FROM dbo.YourTable
WHERE TRY_CONVERT(decimal(10,4),REPLACE(Max_Off,N'%',N''))
  AND Max_Off IS NOT NULL;

Again, fix all your values where it couldn't be converted. Then you can UPDATE the value and then ALTER the table:

UPDATE dbo.YourTable
SET Max_Off = TRY_CONVERT(decimal(6,4),REPLACE(Max_Off,N'%',N'')) / 100.00
GO
ALTER TABLE dbo.YourTable ALTER COLUMN Max_Off decimal(6,4) NOT NUll; --Again, change to NULL if needed

Now your data is fixed, you should have an easier time implementing the logic for IsExcess.