Michael Kaldwid Michael Kaldwid - 1 year ago 71
SQL Question

Select rows WHERE column = value or column is null (current solution is sloppy and slow)

I'm currently developing a web form that

Inserts customer data into a database
. Before insertion, the database needs to be
queried for duplicates
. The fields I'm checking are
Name, Shipping Address, and Billing Address
is a
required field
, and at least
one address is also required
. Right now my (very messy) code looks like this:

string partialQurey;
SqlConnection sqlConn = new SqlConnection(connString);

if(/*both addresses fillied out*/) {
partialQurey = "BillingAddress=@BillingAddress AND ShippingAddress=@ShippingAddress";
else if(/*only billing address is filled out*/) {
partialQurey = "BillingAddress=@BillingAddress AND ShippingAddress IS NULL";
else if(/*only shipping address is filled out*/) {
partialQuery = "BillingAddress IS NULL AND ShippingAddress=@ShippingAddress";
else { throw new Exception("error..."); }

SqlCommand cmd = new SqlCommand("SELECT * FROM Customers WHERE Name=@Name AND " + partalQuery, sqlConn);

cmd.Parameters.AddWithValue("@Name", Name.Text);
cmd.Parameters.AddWithValue("@BillingAddress", BillingAddress.Text, null);
cmd.Parameters.AddWithValue("@ShippingAddress", ShippingAddress.Text, null);

I've been trying to find a more efficent way of doing this with one SQL command and I thought that this code would do it:

FROM Customers
WHERE Name = @Name
AND BillingAccount = ISNULL(@BillingAccount, BillingAccount)
AND ShippingAccount = ISNULL(@ShippingAccount, ShippingAccount)

The idea was to check if @BillingAccount / @ShippingAccount is null, and if they are, the expression would evaluate to BillingAccount = BillingAccount / ShippingAccount = ShippingAccount (which would then evaluate to true).

The problem I ran into is that while value = value is true, null = null is false, making the entire WHERE clause false (because I'm using ANDs).

I'm not really sure where to go from hear.

*Side Note: If you're wondering why my AddWithValue method uses 3 parameters, it's an extension method that convert c# null values into SQL null values. Code is hear if you're interested: http://www.codeproject.com/Tips/310792/Handling-null-values-in-SqlParameter-parameter-obj

Answer Source

One way to prevent duplicated register:

INSERT INTO Customers ( CustomerId )
   SELECT 1 AS CustomerId 
      ( SELECT 1 FROM Customers WHERE CustomerId = 1 );

One way to avoid the null = null is false

       FROM Customers 
      WHERE Name = @Name
        AND ISNULL(BillingAccount, '_') = ISNULL(@BillingAccount, '_')
        AND ISNULL(ShippingAccount, '_') = ISNULL(@ShippingAccount, '_')

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download