CODEWITHSUNDEEP
sqlsql-server
Ruth
Ruth

Reputation: 81

Conversion failed when converting the varchar value 'NULL' to data type int. Why?

I am trying to add a new column called [Scanned] to my SQL Server table, [master].[dbo].[TEST_concat].

However, I get this error:

Msg 245, Level 16, State 1, Line 2
Conversion failed when converting the varchar value 'NULL' to data type int.

Code:

SELECT TOP (11000) 
    filer_id
    ,[filerName]
    ,[ResourceName]
    ,[UniqueIdentity]
    ,[DirID]
    ,[DirsCount]
    ,[PermCount]
    ,[RowID]
    ,[ResourceType]
    ,[ResourceFlags]
    ,[Hresult]
    ,[Details]
    ,[fsid]
    ,[Protocol]
    ,[vExport]
    ,[filerType]
    ,[Error Messages]
FROM 
    [master].[dbo].[TEST_concat]

ALTER TABLE dbo.TEST_concat
ADD [Scanned] AS
    CASE WHEN [DirID] > 0 AND [DirsCount] IS NULL 
            THEN 'in Shares table, but not SortedDirectoryTree properties'
         WHEN [DirID] > 0 AND  [DirsCount] > 0 
            THEN 'Yes'
         WHEN [DirID] IS NULL AND [DirsCount] IS NULL
            THEN 'No'
         ELSE ' ' 
    END

This is what my results look like with the error message:

enter image description here

The results should be all filled out with thousands of lines.

Upvotes: 1

Views: 3978

Answers (1)

Thom A
Thom A

Reputation: 95571

Going to post this as an answer, just to expand a little bit.

Firstly, the problem appears to be that DirID or DirCount aren't the datatype int but are a varchar or similar. Thus, as a result of the data choice, someone has inserted the literal string value 'NULL', rather than the value NULL. If you run the query SELECT CONVERT(int, 'NULL'); you'll see that you get the same error the OP has.

The real solution here, therefore, is to fix the data and the datatype. Firstly, you'll need to find the bad values. This can be done with:

SELECT DirID, DirsCount
FROM [master].[dbo].[TEST_concat]
WHERE (TRY_CONVERT(int,DirID) IS NULL AND DirID IS NOT NULL)
   OR (TRY_CONVERT(int, DirsCount) IS NULL AND DirsCount IS NOT NULL);

I'd check this data over first. if you're happy that all of these values can be updated with NULL then you do 2 separate UPDATE statements (you might not need both of these):

UPDATE [master].[dbo].[TEST_concat]
SET DirID = NULL
WHERE TRY_CONVERT(int,DirID) IS NULL AND DirID IS NOT NULL;

UPDATE [master].[dbo].[TEST_concat]
SET DirsCount = NULL
WHERE TRY_CONVERT(int,DirsCount ) IS NULL AND DirsCount IS NOT NULL;

We should probably, now, look at correcting the datatype. This can be done with:

ALTER [master].[dbo].[TEST_concat] ALTER DirID int;
ALTER [master].[dbo].[TEST_concat] ALTER DirsCount int;

Now, you can run your other ALTER command that you have in your intial post.

Of course, this strongly assumes that DirID and DirsCount should have an int (or decimal) value (if decimal, then replace all references to int with decimal(s,p), where s and p are your required scale and precision). If they can have other values then that's a completely different kettle of fish. I imagine that [DirsCount] > '0' may held incorrect results, as (for example) 'a' > '0'. For example try: 

SELECT CASE WHEN 'a' > '0' THEN 1 ELSE 0 END;

Notice the result value is 1.

Upvotes: 2

Related Questions