SqlParameter，SELECT语句，WHERE子句和DBNull.Value [英] SqlParameter, a SELECT statement, a WHERE clause and DBNull.Value

问题描述

大家好，
我有一条像这样已被参数化的sql语句:

Hi all,
I have a sql statement that I have parameterized like this:

string sql = "SELECT * FROM xyz WHERE col1 = @val1 AND col2 = @val2";

一切正常，直到我必须像这样分配@ val2的空值为止:

Everything works fine until I have to deal with a null value for @val2 that I assign like this:

SqlParameter para1 = new SqlParameter("@val1", DBNull.Value)

SELECT语句不返回任何行.
如果我将查询更改为:

The SELECT statement returns no rows.
If I change the query to:

string sql = "SELECT * FROM xyz WHERE col1 IS NULL AND col2 = @val2";

效果很好.

我知道在sql语句中检查NULL需要使用"IS NULL"，但是我应该如何使用参数化查询来处理它以获得相同的效果?


任何帮助，感激不尽，
最好的问候
Andy

it works fine.

I know that checking for NULL in a sql statement requires to use "IS NULL" but how should I deal with that using a parameterized query in order to get the same effect?


Any help is kindly appreciated,
best regards
Andy

推荐答案

SELECT * FROM xyz WHERE (@VAL1 IS NULL OR col1 IS NULL OR col1 = @val1) AND col2 = @val2

您好，

在IS NULL之前删除"="符号

Hi,

Remove ''='' sign before IS NULL

string sql = "SELECT * FROM xyz WHERE col1 IS NULL AND col2 = @val2";

这篇关于SqlParameter，SELECT语句，WHERE子句和DBNull.Value的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！