Passing potential Null parameters to sql query 1

[fsteeman]

Hi all, I cannot figure out a solution to this problem I am having and I cannot find anything helpful on the internet or on this forum after searching for a while... Any help is therefore greatly appreciated!

I am programming in C# using (strongly typed) datasets. There is one table consisting mostly of foreign keys that are nullable. So if there is nothing to refer to, the value is null. I recently changed this from having zero values in these cases after finally programming the relationships into the SQL SERVER database.

Now I want to be able to search for specific records based on parameters passed from the application layer. These parameters too, are nullable. I would like an exact match of all the foreign key values including any nulls.

The following SQL Statement no longer works after enforcing relationships and replacing all zero values with nulls:

SELECT     taxid_ID, HigherTaxon, Genus, Subgenus, Species, Subspecies, IdentificationQualifier, IdentificationNotes
FROM         TaxonIdentifications
WHERE    (HigherTaxon = @hitaxon) AND (Genus = @genus) AND (Subgenus = @subgenus) AND (Species = @species) AND (Subspecies = @subspecies) AND 
                      (IdentificationQualifier = @idqualifier)

It doesn't work because the proper syntax in case of null values should be 'IS NULL' and not '= NULL'.

So instead I am experimenting with CASE WHEN statements, but I am not coming anywhere with things like this:

SELECT     taxid_ID, HigherTaxon, Genus, Subgenus, Species, Subspecies, IdentificationQualifier, IdentificationNotes
FROM         TaxonIdentifications
WHERE     (HigherTaxon = @hitaxon) AND (Genus = @genus) AND CASE WHEN @subgenus IS NULL THEN (Subgenus IS NULL) ELSE (Subgenus = @subgenus) 
                      END AND (Species = @species) AND CASE WHEN @subspecies IS NULL THEN (Subspecies IS NULL) ELSE (Subspecies = @subspecies) END AND 
                      (IdentificationQualifier = @idqualifier)

Isn't there an easy solution to this that does not require using stored procedures?



Fedor Steeman
Geological Museum Copenhagen
Denmark

 

[fsteeman]

Hi all,

Of course the solution is one that I have been using before but for some reason forgot! I am sorry for this and feel really stupid!

The basic solution to this is using ISNULL statements like this:

SELECT     taxid_ID, HigherTaxon, Genus, Subgenus, Species, Subspecies, IdentificationQualifier, IdentificationNotes
FROM         TaxonIdentifications
WHERE     (HigherTaxon = @hitaxon) AND (Genus = @genus) AND (Subgenus = ISNULL(@subgenus, Subgenus)) AND (Species = @species) AND 
                      (Subspecies = ISNULL(@subspecies, Subspecies)) AND (IdentificationQualifier = @idqualifier)

However, for some reason this too won't work, whereas it should. Can anyone verify the correctness of the sql sentence here?


Fedor Steeman
Geological Museum Copenhagen
Denmark

 

[bborissov]

NULL can't be equal or different to any value, even NULL.
I am not sure what you want. Did you want ALL records when you pass @subgenus = NULL (that match all other conditions of course) or you want only these records where Subgenus = NULL?

Borislav Borissov
VFP9 SP1, SQL Server 2000/2005.
MVP VFP

 

[fsteeman]

I want all records that only match all values, including the null values. So records with e.g. values 21, 566, null, 194, null, 4 should be picked out, not with values 21, 566, null, 194, 2, 4


Fedor Steeman
Geological Museum Copenhagen
Denmark

 

[Catadmin]

I'm not sure if this is what you're looking for, but check it out and let me know if it worked or not:

SELECT     taxid_ID, HigherTaxon, Genus, Subgenus, Species, Subspecies, IdentificationQualifier, IdentificationNotes
FROM         TaxonIdentifications
WHERE [b]([/b]HigherTaxon = @hitaxon 
     AND Genus = @genus 
     AND Subgenus = @subgenus 
     AND Species = @species AND Subspecies = @subspecies 
     AND IdentificationQualifier = @idqualifier [b])
OR (HigherTaxon IS NULL 
    OR Genus IS NULL 
    OR Subgenus IS NULL 
    OR Species IS NULL 
    OR Subspecies IS NULL 
    OR IdentificationQualifier IS NULL) [/b]

BTW, I removed a lot of the unnecessary parens in your WHERE clause, because they make it hard for me to read. If you really want them, you can easily put them back in, but they are truly unnecessary in this particular query. Otherwise, all the bolded stuff is what I've changed.




Catadmin - MCDBA, MCSA
"No, no. Yes. No, I tried that. Yes, both ways. No, I don't know. No again. Are there any more questions?"
-- Xena, "Been There, Done That"

 

[bborissov]

SELECT taxid_ID,
       HigherTaxon,
       Genus,
       Subgenus,
       Species,
       Subspecies,
       IdentificationQualifier,
       IdentificationNotes
FROM TaxonIdentifications
WHERE (HigherTaxon = @hitaxon)                         AND
      (Genus = @genus)                                 AND
      (Subgenus IS NULL OR Subgenus = @subgenus)       AND
      (Species = @species)                             AND
      (Subspecies IS NULL OR Subspecies = @subspecies) AND
      (IdentificationQualifier = @idqualifier)

Is that what you want?

Borislav Borissov
VFP9 SP1, SQL Server 2000/2005.
MVP VFP

 

[fsteeman]

Hi & thanks a lot for trying to help me!

I tried it out and at least now there was a result with my target row among it, but also all other rows with null values. The rows with null values should only be selected if the corresponding parameters are null.

I am trying to get a specific row which is the only one with these values:

HigherTaxon: 593
Genus: 556
Subgenus: null
Species: 1175
Subspecies: null
IdentificationQualifier: 4

I am not interested in other rows where these columns also happen to be null. This is why I am trying with the ISNULL method. The problem is, I guess, that NULL never can be equal to anything, including itself.



Fedor Steeman
Geological Museum Copenhagen
Denmark

 

[fsteeman]

Hi Boris,

I replied to the other post by Catadmin. I will check out your solution right away!

Cheers,

Fedor

Fedor Steeman
Geological Museum Copenhagen
Denmark

 

[fsteeman]

Boris, I think your solution did the trick! I thank and salute you!

Now I have to figure out what exactly it does, but it seems simply a question of adding more OR-conditions in more intricate ways.

Thanks guys you saved my day!



Fedor Steeman
Geological Museum Copenhagen
Denmark

 

[fsteeman]

I can't believe the solution was so simple! I was going about this in way too complicated ways!


Fedor Steeman
Geological Museum Copenhagen
Denmark

 

[Catadmin]

Glad we could help. @=)



Catadmin - MCDBA, MCSA
"No, no. Yes. No, I tried that. Yes, both ways. No, I don't know. No again. Are there any more questions?"
-- Xena, "Been There, Done That"