1

I mean I can't query null values when column type sql_variant

For example docsdate table look like this:

ValID DocID     Value  <--sql variant column)

 1. 488 146      30.10.2007
 2. 740 190      31.03.2008
 3. 570 161      31.10.2008 
 4. 242 103      NULL
 5. 248 104      NULL

When query like select * from docsdate where value is null no rows returned

Any idea?

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
aeon
  • 11
  • 3

1 Answers1

1

This works fine for me. Are you sure your NULL values are actually proper NULLs and not strings containing the text NULL? Do they show up with a yellow background in SSMS?

create table #docsdate 
(
ValID int,
DocID int,
value sql_variant
)

INSERT INTO #docsdate
SELECT 488,146,'30.10.2007' UNION ALL
SELECT 740,190,'31.03.2008' UNION ALL
SELECT 570,161,'31.10.2008' UNION ALL 
SELECT 242,103,NULL UNION ALL
SELECT 248,104,NULL

SELECT * FROM #docsdate
WHERE value IS NULL

DROP TABLE #docsdate
Martin Smith
  • 438,706
  • 87
  • 741
  • 845