SQL语句可以是“IS NULL”吗？用作SqlCommand参数？ [英] Can the SQL statement "IS NULL" be used as a SqlCommand Parameter?

问题描述

Hy，



是否可以在带有参数的SQL startment中创建一个IS NULLclausel？



我尝试了两种代码

Hy,

Is it posible the make a "IS NULL" clausel in a SQL statament with an parameter?

I tried two tipes of code

string command = "SELECT COUNT(*) AS Expr1 " + 
                            " FROM     Warehouse1Movements " +
                            " WHERE  (Deleted = 0) AND (OutDate=@OutDate) AND (LocationNumber = @LocationNumber)";

                SqlConnection conn = new SqlConnection(Properties.Settings.Default.ICSDB);
                conn.Open();

                SqlCommand cmd = new SqlCommand(command, conn);
                cmd.Parameters.Add("@LocationNumber", SqlDbType.Int).Value = locationNumber;
                cmd.Parameters.AddWithValue("@OutDate", DBNull.Value);

                int count = 0;
                int.TryParse(cmd.ExecuteScalar().ToString(), out count);

                conn.Close();

这给了我一个结果。



第二个是真正的计数值：

This gives me a fals result.

And the second one the true count value:

string command = "SELECT COUNT(*) AS Expr1 " + 
                            " FROM     Warehouse1Movements " +
                            " WHERE  (Deleted =0) AND (OutDate IS NULL) AND (LocationNumber = @LocationNumber)";

                SqlConnection conn = new SqlConnection(Properties.Settings.Default.ICSDB);
                conn.Open();

                SqlCommand cmd = new SqlCommand(command, conn);
                cmd.Parameters.Add("@LocationNumber", SqlDbType.Int).Value = locationNumber;

                int count = 0;
                int.TryParse(cmd.ExecuteScalar().ToString(), out count);

                conn.Close();

有没有办法使用参数执行此操作还是必须始终更改sql命令字符串以执行此操作？

Is there a way to do this with parameters or must I always change the sql command string to perform this?

推荐答案

您可以编写（OutDate = @ OutDate）



as

You can write (OutDate=@OutDate)

as

(Isnull(OutDate,'Jan 01 1900')=Isnull(@OutDate,'Jan 01 1900'))

这篇关于SQL语句可以是“IS NULL”吗？用作SqlCommand参数？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！