我如何通过一个为DBNull值参数化的SELECT语句？ [英] How do I pass a DBNull value to a parameterized SELECT statement?

问题描述

我在C＃中的SQL语句（.NET Framework 4的运行对SQL Server 2K8），看起来是这样的：

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

上面的查询工作正常以下ADO.NET code：

The above query works fine with the following ADO.NET code:

DbParameter parm = Factory.CreateDbParameter();
parm.Value = "SomeValue";
parm.ParameterName = "@Column2";
//etc...

该查询返回零行，不过，如果我分配到的DBNull.Value的DbParameter的Value即使有在列2空值。如果我更改查询专门容纳空测试：

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

我得到一个附近有语法错误@列2'异常运行时。请问有没有办法，我可以在SELECT语句的WHERE子句中使用空则为DBNull作为参数？

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?

推荐答案

您可以使用

SELECT [Column1] FROM [Table1] WHERE [Column2] = ISNULL(@Column2 , 'value');

价值可以是ü希望它重新present任何价值。如果你想匹配列2具有空列，

'value' can be any value that u want it to represent. If you want to match column2 that has null columns,

SELECT [Column1] FROM [Table1] WHERE ISNULL([Column2], 'value') = ISNULL(@Column2 , 'value');

这篇关于我如何通过一个为DBNull值参数化的SELECT语句？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！