Click here to Skip to main content
15,885,216 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
Hy,

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

I tried two tipes of code

C#
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:

C#
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();



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

1 solution

You can write (OutDate=@OutDate)

as
SQL
(Isnull(OutDate,'Jan 01 1900')=Isnull(@OutDate,'Jan 01 1900'))
 
Share this answer
 
Comments
TarikHuber 20-Nov-14 3:28am    
The code above is just a example. I use dinamicaly generated sql commands with parameters. The code above I wrote manualy to se the diference and there is one. Can it be done without changing the SQql command string (only over parameters)?
Tomas Takac 20-Nov-14 3:50am    
If you are using SQL server you can set SET ANSI_NULLS OFF[^] so NULL=NULL yields true. But I would consider it a hack rather than proper solution.
Shweta N Mishra 20-Nov-14 4:01am    
yes, correct.
Shweta N Mishra 20-Nov-14 3:37am    
No, If you already writing a dynamic query then you can apply above or change your dynamic query based on parameter value.
TarikHuber 20-Nov-14 3:38am    
Bad news but I thing thats the only solution :(
Thx for the Help!

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900