dcsimg
Results 1 to 19 of 19

Thread: Ignore Null parameters in a single query

  1. #1

    Thread Starter
    Lively Member
    Join Date
    Sep 2009
    Posts
    96

    Ignore Null parameters in a single query

    I'm programming a form where people select information to limit information they want to see. There are 10 fields, and they can choose between 0 and 10. When they click the command button, the code lifts the values out of each of the 10 comboboxes and filters it in a sql query. Is there a way to get the Select statement to disregard operations on columns assigned null parameters? Could I assign * to the null fields and have it read out like an asterisk in SQL?

  2. #2
    Fanatic Member
    Join Date
    Jun 2004
    Location
    All useless places
    Posts
    917

    Re: Ignore Null parameters in a single query

    You can form your SQL like
    Code:
    select col1, col2
    from table1
    where col1 = ISNULL(@col1ValueToCompare, col1)
    and similar for all the columns.

  3. #3

    Thread Starter
    Lively Member
    Join Date
    Sep 2009
    Posts
    96

    Re: Ignore Null parameters in a single query

    I figured it out with temp tables...but it won't let me do this (@ln is parameter)

    [code/]
    If @ln is not null
    SELECT * Into #Temp1 FROM [#Lots] WHERE [Lot Number] = @ln
    Else
    Select * Into #Temp1 From #Lots
    Drop table #Lots
    [/code]

    even though the conditionals are mutually exclusive. I have a chain of about 10, as I said, or else I'll end up making 2^10 temps....the replace feature won't work, it won't accept an asterisk and I don't want to filter results if the parameter is null

  4. #4
    Fanatic Member
    Join Date
    Jun 2004
    Location
    All useless places
    Posts
    917

    Re: Ignore Null parameters in a single query

    Temp table is not required here, I believe. Did you try implementing ISNULL?

  5. #5
    Super Moderator si_the_geek's Avatar
    Join Date
    Jul 2002
    Location
    Bristol, UK
    Posts
    41,413

    Re: Ignore Null parameters in a single query

    You seem to be having problems with the code tags.. either use the button for it, or type the tags properly: [code] code here [/code]


    I think your issue can be solved by using the method shown in the article Stored procedures: How can I add optional parameters to a Where clause, without building the statement in a string? from our Database Development FAQs/Tutorials (at the top of this forum)

    It is a little different to rjv_rnjn's suggestion, but basically the same idea.

  6. #6
    Fanatic Member
    Join Date
    Jun 2004
    Location
    All useless places
    Posts
    917

    Re: Ignore Null parameters in a single query

    @si_the_geek: Is there a reason you suggest checking NULL's that way (performance wise or some known issues)?

    I think using ISNULL() is quite clean when you have to do that for multiple parameters.

  7. #7

    Thread Starter
    Lively Member
    Join Date
    Sep 2009
    Posts
    96

    Re: Ignore Null parameters in a single query

    thank you for the code tags...I've asked every responder the proper tags and you're the first to tell me.

    If I used isnull, wouldn't I be replacing the null parameter with a value, and therefore run an unwanted query on the table? If the parameter is null, then I don't want to run a query, I want to move to the next parameter. If the parameter is not null, I want to filter the corresponding column. And there are 10 different parameters that I may or may not filter in this way.

  8. #8
    Fanatic Member
    Join Date
    Jun 2004
    Location
    All useless places
    Posts
    917

    Re: Ignore Null parameters in a single query

    If I used isnull, wouldn't I be replacing the null parameter with a value, and therefore run an unwanted query on the table?
    I think I'm missing your point.
    Do you mean each column that you are looking at belongs to a separate table? In that case your query could look like
    Code:
     select t1.Col1, t2.Col2
    from table1 t1
    inner join table2 t2 on t1.id = t2.id
    where t1.col1 = ISNULL(@col1ValueToCompare, col1) AND t2.col2 = ISNULL(@col2ValueToCompare, t2.col2)
    & so on.

    If you are worried about the extra reads that the SQL server has to perform for the fetch to occur (in case of ISNULL), then you shouldn't -- it's equivalent to if it's not there. Even if there's a performance hit, it's minimal (I've never tried to get any stats on that).

    BUT, if you mean that if column1 is selected as filter then you look for values from Table1 then you can use the if..else. It still doesn't make clear why would you use temp tables.

    A more detailed explanation of your problem could help us give you a suggestion.

  9. #9
    Super Moderator si_the_geek's Avatar
    Join Date
    Jul 2002
    Location
    Bristol, UK
    Posts
    41,413

    Re: Ignore Null parameters in a single query

    Quote Originally Posted by rjv_rnjn View Post
    @si_the_geek: Is there a reason you suggest checking NULL's that way (performance wise or some known issues)?

    I think using ISNULL() is quite clean when you have to do that for multiple parameters.
    The method in the FAQ article is generic SQL so can be used for most database systems, whereas IsNull is specific to a particular DBMS (most have something similar, but you need to know the name and syntax each time).

    IsNull is shorter to write, but the method in the FAQ is likely to run more quickly - as I will explain below.
    Quote Originally Posted by theguyinthehat
    If I used isnull, wouldn't I be replacing the null parameter with a value, and therefore run an unwanted query on the table?
    In theory yes - for each row it will compare the field to itself... but the database system might be clever enough to notice that there is no point, and just skip the work.

    Using the method in the FAQ virtually guarantees that the work will be skipped, which is explained by this piece of text in the article:
    The first condition will only be checked once for the entire query (just as the If statement was), because the value is fixed at the time the query starts - it does not change for each row.

    When the first condition is True, the second will not be checked at all - because the result of an Or will be True if either or both parts are True (ie: because the first condition is True, it doesn't matter whether or not the second condition is)

  10. #10
    Fanatic Member
    Join Date
    Jun 2004
    Location
    All useless places
    Posts
    917

    Re: Ignore Null parameters in a single query

    Good to know that.
    Interestingly, it seems I never reached the bottom of the MSDN article or conveniently forgot it
    Quote Originally Posted by MSDN
    C. Testing for NULL in a WHERE clause
    Do not use ISNULL to find NULL values. Use IS NULL instead. The following example finds all products that have NULL in the weight column. Note the space between IS and NULL.

  11. #11
    Super Moderator si_the_geek's Avatar
    Join Date
    Jul 2002
    Location
    Bristol, UK
    Posts
    41,413

    Re: Ignore Null parameters in a single query

    I presume that comment is actually referring to a different situation, but it is hard to be sure without seeing the rest of the article.

    I haven't tested, but for this situation I wouldn't be surprised if your method is actually equal to the FAQ method (which is equal to [or better than] using an If statement to build the query).

  12. #12

    Thread Starter
    Lively Member
    Join Date
    Sep 2009
    Posts
    96

    Re: Ignore Null parameters in a single query

    Ok--I need separate tables because I need to run subsequent queries on tables that have already been filtered. This would do the job, if it would compile (perhaps not very efficiently, but it would do it):
    Code:
    ALTER PROCEDURE [RSTA\TAHuth].[spRelotter]
    @ln VARCHAR(4),
    @ton VARCHAR(20),
    @cn VARCHAR(10),
    @En VARCHAR(30),
    @Wn VARCHAR(5),
    @ten VARCHAR(30),
    @pn VARCHAR(20),
    @dn VARCHAR(5) 
    
    AS
    SELECT * INTO #Lots From [FPAD Lot Data]
    
    If @ln is not null
    SELECT * Into #Temp1 FROM [#Lots] WHERE [Lot Number] = @ln
    Else
    Select * Into #Temp1 From #Lots
    Drop table #Lots
    
    If @ton is not null
    SELECT * into #Temp2 FROM #Temp1 WHERE [Tool] = @ton
    Else
    SELECT * into #Temp2 FROM #Temp1
    Drop Table #Temp1
    
    If @cn is not null
    Select * into #Temp3 From #Temp2 WHERE [Charge Number] = @cn
    Else
    Select * into #Temp3 From #Temp2
    Drop #Temp2
    
    
    --etcetera, but it won't compile!!!
    --Finally, output to vb.net
    SELECT * INTO [#Lots] FROM [#Temp9]
    
    	/* SET NOCOUNT ON */ 
    	RETURN

    I don't understand how you want me to use Is null or isnotNull()... hopefully you can understand what i'm trying to do, I wasn't very clear before. I have to do the 'else' query or I'd have so many different queries to run to cover every permutation of non-null values.

  13. #13
    VB Addict Pradeep1210's Avatar
    Join Date
    Apr 2004
    Location
    Inside the CPU...
    Posts
    6,614

    Re: Ignore Null parameters in a single query

    Instead of using temp tables in this manner, I think what rjv_rnjn suggested would be much more efficient with same output result.
    Pradeep, Microsoft MVP (Visual Basic)
    Please appreciate posts that have helped you by clicking icon on the left of the post.
    "A problem well stated is a problem half solved." Charles F. Kettering

    Read articles on My Blog 101 LINQ Samples JSON Validator XML Schema Validator "How Do I" videos on MSDN VB.NET and C# Comparison Good Coding Practices VBForums Reputation Saver String Enum Super Simple Tetris Game


    (2010-2013)
    NB: I do not answer coding questions via PM. If you want my help, then make a post and PM me it's link. If I can help, trust me I will...

  14. #14

    Thread Starter
    Lively Member
    Join Date
    Sep 2009
    Posts
    96

    Re: Ignore Null parameters in a single query

    I think that would return many different instances of queries on the same table rather than compounding all the relevant (ie. non-null) queries on the same table. I could take the intersection of all the different queries and get the same result...let me see if there's a way to do that? I don't see how a chain of isnull() would compound the queries...but I could be missing it completely

  15. #15
    Fanatic Member
    Join Date
    Jun 2004
    Location
    All useless places
    Posts
    917

    Re: Ignore Null parameters in a single query

    Try this:
    Code:
    ALTER PROCEDURE [RSTA\TAHuth].[spRelotter]
    @ln VARCHAR(4),
    @ton VARCHAR(20),
    @cn VARCHAR(10),
    @En VARCHAR(30),
    @Wn VARCHAR(5),
    @ten VARCHAR(30),
    @pn VARCHAR(20),
    @dn VARCHAR(5) 
    
    AS
    
    SELECT * FROM [FPAD Lot Data]
    WHERE ((@ln IS NULL) OR (@ln = [Lot Number]))
    AND ((@ton IS NULL) OR (@ton = [Tool]))
    AND ((@cn IS NULL) OR (@cn = [Charge Number]))
    /*And similarly for other variables like @En, @Wn etc..*/
    END

  16. #16
    Fanatic Member
    Join Date
    Jun 2004
    Location
    All useless places
    Posts
    917

    Re: Ignore Null parameters in a single query

    I re-read your post and this line struck me
    Quote Originally Posted by theguyinthehat
    Ok--I need separate tables because I need to run subsequent queries on tables that have already been filtered.
    It makes me think you do not view SQL queries as set based but procedural in nature. I would suggest going through this article that explains this thing in much more detail (and in a better way than I can).

  17. #17

    Thread Starter
    Lively Member
    Join Date
    Sep 2009
    Posts
    96

    Re: Ignore Null parameters in a single query

    of course... Yes that makes sense. Really wish I'd thought of that. That's embarrassing. Thanks a lot!

    And I'll read it. I'm a Materials engineer, and now having to organize all this programming...getting used to it.

  18. #18
    Fanatic Member
    Join Date
    Jun 2004
    Location
    All useless places
    Posts
    917

    Re: Ignore Null parameters in a single query

    Quote Originally Posted by si_the_geek View Post
    I presume that comment is actually referring to a different situation, but it is hard to be sure without seeing the rest of the article.

    I haven't tested, but for this situation I wouldn't be surprised if your method is actually equal to the FAQ method (which is equal to [or better than] using an If statement to build the query).
    The comment is for checking null values in where condition:
    Code:
    SELECT Name, Weight
    FROM Production.Product
    WHERE Weight IS NULL;
    Performance wise I had tested initially using ISNULL() over "IS NULL" and didn't find any time deterioration for some huge data set that I had (I was looking only in seconds). So it may be equivalent in performance; but I was "sold" by the logic that you had for using it.
    {I even used it already for the sample code given to OP}.

  19. #19
    Fanatic Member
    Join Date
    Jun 2004
    Location
    All useless places
    Posts
    917

    Re: Ignore Null parameters in a single query

    Quote Originally Posted by theguyinthehat View Post
    of course... Yes that makes sense. Really wish I'd thought of that. That's embarrassing. Thanks a lot!

    And I'll read it. I'm a Materials engineer, and now having to organize all this programming...getting used to it.
    Glad to be of help!

    Please mark the thread as resolved if there are no follow-up questions.

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Featured


Click Here to Expand Forum to Full Width