CODEWITHSUNDEEP
c#sql-server
jamieivy
jamieivy

Reputation: 33

SQL query using ISNULL

I'm writing the following SQL query using the TableAdapter Query Configuration Wizard in Visual Studio.

SELECT  COUNT(*) AS census
FROM Inventory INNER JOIN Taxonomy ON Inventory.GlobalID = Taxonomy.GlobalID
WHERE (Inventory.Institution = @institution) AND (Inventory.Year = @year) AND 
  (Inventory.Nending > 0)

I'm trying to add the following criteria to the WHERE clause:

(Taxonomy.Class = ISNULL(@class, Taxonomy.Class))

so that either
1) only rows that match the @class input parameter are returned or
2) all rows are returned regardless of their TaxonomyGlobal.Class value.

When I add this statement to the query my C# code that calls the query throws a System.ArgumentNullException error and states the @class value cannot be null.

Any help on how to add this criterion to the WHERE clause would be appreciated.

C# code:

namespace CollectionMetrics
{
  class DatabaseQueries
  {
      QueryDataSetTableAdapters.InventoryTableAdapter queryAdapter = 
            new QueryDataSetTableAdapters.InventoryTableAdapter();

      public void CensusQuery(string institution, short year, string xclass)
      {
            int census = 0;
            string localClass = xclass;
            if (xclass == "All Classes") localClass = null;

            census = (int)queryAdapter.CensusBySpecies(localClass, institution, year);
            censusOutput.Add(census);
      }
  }
}

Upvotes: 2

Views: 607

Answers (2)

Josh K
Josh K

Reputation: 765

SQL:

(@class IS NULL OR Taxonomy.Class = @class)

Since you are using TableAdapter, you will need to edit the field to allow nulls:

https://msdn.microsoft.com/en-us/library/ms233762.aspx

Setting the AllowDbNull Property

To enable a query to accept null values In the Dataset Designer, select the TableAdapter query that needs to accept null parameter values. Select Parameters in the Properties window and click the ellipsis (…) button to open the Parameters Collection Editor. Select the parameter that allows null values and set the AllowDbNull property to true.

If you are using SqlParameters:

C#

 var param = new SqlParameter("@class", (object) classVariable ?? DBNull.Value);

Replace classVariable with the name of the variable you are using in your code to set the value for the @class SqlParameter. The cast to object is required because the variable does not have the same type as DBNull.

Upvotes: 2

Tab Alleman
Tab Alleman

Reputation: 31775

I once tried doing what you're trying to do, thinking this was a nifty way to ignore parameters that weren't passed from the front end (and therefore were NULL).

But then I learned that using ISNULL() in the WHERE clause like this prevents indexes from being used, making your query much SLOWER than if you used: 

WHERE (Taxonomy.Class = @Class OR @Class IS NULL)

Unintuitive, I admit; the way you're trying looks like it would be cleaner and therefore faster, but for SQL performance, the most important thing is using available indexes, and so it turns out the A OR B approach is actually faster than the ISNULL() approach you want to use.

As to why you're getting an error, it's got to be something the wizard is enforcing. If you tried your query purely in SQL (using SSMS), it would allow it. UNLESS your query is actually in a stored procedure and @Class is a required parameter.

Upvotes: 0

Related Questions