How do I pass a DBNull value to a parameterized SELECT statement?
- by Dan
I have a SQL statement in C# (.NET Framework 4 running against SQL Server 2k8) that looks like this:
SELECT [Column1] FROM [Table1] WHERE [Column2] = @Column2
The above query works fine with the following ADO.NET code:
DbParameter parm = Factory.CreateDbParameter();
parm.Value = "SomeValue";
parm.ParameterName = "@Column2";
//etc...
This query returns zero rows, though, if I assign DBNull.Value to the DbParameter's Value member even if there are null values in Column2. If I change the query to accommodate the null test specifically:
SELECT [Column1] FROM [Table1] WHERE [Column2] IS @Column2
I get an "Incorrect syntax near '@Column2'" exception at runtime. Is there no way that I can use null or DBNull as a parameter in the WHERE clause of a SELECT statement?
