SitePoint Sponsor

User Tag List

Results 1 to 14 of 14
  1. #1
    SitePoint Wizard boxhead's Avatar
    Join Date
    Feb 2004
    Location
    UK
    Posts
    1,040
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    is "%" + Null still null?

    if i do:

    Code:
    mycolumn LIKE ISNULL('%'+@Param+'%', mycolumn)
    when @Param is set to null is '%'+@Param+'%' still null and therefore the mycolumn is set to equal itself? Or does the second part never run because the first always has a value?

    cheers
    monkey - the rest is history

  2. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,022
    Mentioned
    53 Post(s)
    Tagged
    2 Thread(s)
    Quote Originally Posted by boxhead View Post
    when @Param is set to null is '%'+@Param+'%' still null and therefore the mycolumn is set to equal itself?
    that woulda been my guess, yeah, assuming the plus sign means string concatenation

    what happened when you tested it?
    r937.com | rudy.ca | Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  3. #3
    Non-Member
    Join Date
    Apr 2004
    Location
    Miami, FL, USA
    Posts
    449
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    To be sure you get the results you're looking for, you could do the following:

    LIKE (CASE WHEN @Param IS NULL THEN mycolumn ELSE '%'+@Param+'%', END)

  4. #4
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,022
    Mentioned
    53 Post(s)
    Tagged
    2 Thread(s)
    steve, that's the same as the ISNULL/COALESCE function

    (and you've got a spurious comma in there)

    however, rather than using code which will end up generating mycolumn LIKE mycolumn, i would prefer to see the stored proc test @Param and then ~not~ generate the LIKE comparison at all
    r937.com | rudy.ca | Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  5. #5
    Non-Member
    Join Date
    Apr 2004
    Location
    Miami, FL, USA
    Posts
    449
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by r937 View Post
    steve, that's the same as the ISNULL/COALESCE function
    Not exactly - ISNULL / COALESCE uses the condition as the value

    I used @Param as the condition and '%'+#Param+'%' as the value.

    Look again:
    Code:
    LIKE (CASE WHEN @Param IS NULL THEN mycolumn ELSE '%'+@Param+'%' END)
    Minus the comma, it will work, even if '%'+null != null

  6. #6
    Non-Member
    Join Date
    Apr 2004
    Location
    Miami, FL, USA
    Posts
    449
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by r937 View Post
    rather than using code which will end up generating mycolumn LIKE mycolumn, i would prefer to see the stored proc test @Param and then ~not~ generate the LIKE comparison at all
    Good point, though.... I was just focused on answering his question, not considering the overall performance of it lol.

  7. #7
    Non-Member
    Join Date
    Apr 2004
    Location
    Miami, FL, USA
    Posts
    449
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Perhaps the simplest solution would be:

    Code sql:
    WHERE (@Param IS NULL 
        OR mycolumn LIKE '%'+@Param+'%')

    If @param is null, it should never get to the like comparison.

  8. #8
    SitePoint Wizard boxhead's Avatar
    Join Date
    Feb 2004
    Location
    UK
    Posts
    1,040
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    After some testing we found that the first statement is still null even with wild cards - therefore the column is set to itself.

    We did further testing and wondered if this would be a better way of doing this:

    Code:
    declare @test nvarchar(50)
    
    
    select test from davetest
    
    where (@Test is null          OR
                test like '%' + @test + '%')
    monkey - the rest is history

  9. #9
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,022
    Mentioned
    53 Post(s)
    Tagged
    2 Thread(s)
    i would prefer to see something along these lines (using pseudocode) --
    Code:
    IF @Test IS NULL
    THEN 
       SELECT test FROM davetest
    ELSE
       SELECT test FROM davetest
        WHERE test LIKE '%' + @test + '%'       
    ENDIF
    r937.com | rudy.ca | Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  10. #10
    SitePoint Wizard boxhead's Avatar
    Join Date
    Feb 2004
    Location
    UK
    Posts
    1,040
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by r937 View Post
    i would prefer to see something along these lines (using pseudocode) --
    Code:
    IF @Test IS NULL
    THEN 
       SELECT test FROM davetest
    ELSE
       SELECT test FROM davetest
        WHERE test LIKE '%' + @test + '%'       
    ENDIF
    Not really an option as this would mean writing the same huge SQL statement 4 or 5 times in the code. We also like to keep our SQL in the database and our code on the pages
    monkey - the rest is history

  11. #11
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,022
    Mentioned
    53 Post(s)
    Tagged
    2 Thread(s)
    okay, so instead of multiple queries, construct a single sql string and then EXEC it
    Code:
    sqlstring = 'SELECT test FROM davetest WHERE 1=1 '
    IF @Test IS NOT NULL
    THEN
       sqlstring = sqlstring + 'AND test LIKE ''%'' + @test + ''%'''       
    ENDIF 
    IF @somethingelse IS NOT NULL
    THEN
       sqlstring = sqlstring + 'AND somethingelse LIKE ''%'' + @somethingelse + ''%'''       
    ENDIF 
    EXEC(sqlstring)
    r937.com | rudy.ca | Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  12. #12
    Non-Member
    Join Date
    Apr 2004
    Location
    Miami, FL, USA
    Posts
    449
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I vote for the "OR" clause... the conditional query construction will likely save minimal overhead, and adds way too much complexity to the sproc... the required maintenance of an app built like that doesn't cost-justify the added performance... will be cheaper to buy a bigger server than hire a DBA to manage your complex sprocs.

  13. #13
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,022
    Mentioned
    53 Post(s)
    Tagged
    2 Thread(s)
    Quote Originally Posted by transio View Post
    the required maintenance of an app built like that doesn't cost-justify the added performance... will be cheaper to buy a bigger server than hire a DBA to manage your complex sprocs.
    whoa, that's hyperbolic

    if i tole you oncet, i tole you a thousand times, don't exaggerate

    r937.com | rudy.ca | Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  14. #14
    Non-Member
    Join Date
    Apr 2004
    Location
    Miami, FL, USA
    Posts
    449
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by r937 View Post
    whoa, that's hyperbolic

    if i tole you oncet, i tole you a thousand times, don't exaggerate

    Honestly, your solution is optimal, sure, but honestly probably not more than 10% faster than what I suggested with the OR clause (if that), and requires significantly more work to decipher - even for a senior resource.

    In my above statement, I'm not talking about one query... but about general philosophy... to build out an app with highly complex stored procedures as a basis generally involves the requirement for high-level resources to manage it... I've been involved in such developments in the past... where DBAs overly-complicate the DB layer at the sproc / UDF level for (at best) marginal increases in performance, just to justify their 6-figure salaries.

    To me, the ideal is have a DB layer that's maybe 90-95% optimal yet requires neither a DBA nor senior developer to decipher.


Bookmarks

Posting Permissions

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