jeudi 19 mars 2015

SQL null image to VARBINARY(MAX) Results in Empty

Here is the scenario. We have a column of type IMAGE in a table that holds compressed data. This column can be null. In a different version, we moved this data to a separate table, still an image column.


We have a stored procedure that is checking where the data is, in the original table or the new table. To do that, it selects the value into a VARBINARY(MAX) variable, checks if it is null, if so grabs it from the new table.


Something strange is going on with the checks. For a few rows in the database, the column displays as NULL, but when cast to VARBINARY(MAX) it ends up being an empty value of 0x. This is only for 4 rows out of 426, all others evaluate to NULL fine. If you check for null in the table itself, like WHERE <column> IS NULL it evalulates to true, but if you do WHERE CAST(<column> as VARBINARY(MAX)) IS NULL it evaluates to false.


I can change around my stored procedure so that it handles correctly, but I'm a little baffled as to what is going on here. Any ideas?


This is on SQL Server 2005.


Aucun commentaire:

Enregistrer un commentaire