Is "%" + Null still null?

if i do:

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

that woulda been my guess, yeah, assuming the plus sign means string concatenation

what happened when you tested it?

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)

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

Not exactly - ISNULL / COALESCE uses the condition as the value

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

Look again:


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

Minus the comma, it will work, even if ‘%’+null != null :slight_smile:

Good point, though… I was just focused on answering his question, not considering the overall performance of it lol. :slight_smile:

Perhaps the simplest solution would be:


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

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

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:


declare @test nvarchar(50)


select test from davetest

where (@Test is null          OR
            test like '%' + @test + '%')


i would prefer to see something along these lines (using pseudocode) –


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 :slight_smile:

okay, so instead of multiple queries, construct a single sql string and then EXEC it


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)

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.

whoa, that’s hyperbolic

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

:smiley:

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.