SQL query using ISNULL

Question

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);
      }
  }
}

ISNULL doesn't tell you whether an expressin is null or not, but simply replaces an null values with a specified value. — David P
– David P, Commented Jul 27, 2016 at 18:39
@DavidP, I think he gets that. Look closely once again at how ISNULL is being used here. — Tab Alleman
– Tab Alleman, Commented Jul 27, 2016 at 18:45
Please provide your complete C# code related to this question, that will help us better to understand you — Surendra
– Surendra, Commented Jul 27, 2016 at 18:48

Josh K · Accepted Answer · 2016-07-27 19:38:09Z

2

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.

edited Jul 27, 2016 at 19:38

answered Jul 27, 2016 at 18:36

Josh K

7653 silver badges11 bronze badges

Sign up to request clarification or add additional context in comments.

7 Comments

Josh K Over a year ago

Did you use the code in my edit where you are passing DbNull.Value if the original variable is null?

Josh K Over a year ago

You will have to replace classVariable with whatever variable you are using to set the value for the @class parameter. For DBNull, sorry, capitalize the B

Josh K Over a year ago

See my updated answer. You have to add the cast to object in there, then it should work. msdn.microsoft.com/en-us/library/ms173224.aspx

Josh K Over a year ago

Can you edit the code for CensusBySpecies? Whichever method finally builds the query should be the one handling the possible NULL value, which is where the code I provided should be (or updated code if yours is not using SqlQueryParameters.

jamieivy Over a year ago

I can edit the code for CensusBySpecies, but its just the SQL query within the TableAdapter. Visual Studio won't let me put anything else in there.

|

Tab Alleman · Accepted Answer · 2016-07-27 18:46:49Z

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.

Collectives™ on Stack Overflow

SQL query using ISNULL

2 Answers 2

7 Comments

Comments

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

7 Comments

Comments

Related