boxhead — 2011-03-21T07:23:41-04:00 — #1
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?
r937 — 2011-03-21T08:02:23-04:00 — #2
that woulda been my guess, yeah, assuming the plus sign means string concatenation
what happened when you tested it?
system — 2011-03-21T08:09:59-04:00 — #3
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)
r937 — 2011-03-21T08:20:51-04:00 — #4
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
system — 2011-03-21T08:50:52-04:00 — #5
Not exactly - ISNULL / COALESCE uses the condition as the value
I used @Param as the condition and '%'+#Param+'%' as the value.
LIKE (CASE WHEN @Param IS NULL THEN mycolumn ELSE '%'+@Param+'%' END)
Minus the comma, it will work, even if '%'+null != null
system — 2011-03-21T08:55:35-04:00 — #6
Good point, though.... I was just focused on answering his question, not considering the overall performance of it lol.
system — 2011-03-21T08:57:27-04:00 — #7
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.
boxhead — 2011-03-21T09:10:45-04:00 — #8
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 + '%')
r937 — 2011-03-21T09:16:51-04:00 — #9
i would prefer to see something along these lines (using pseudocode) --
IF @Test IS NULL
SELECT test FROM davetest
SELECT test FROM davetest
WHERE test LIKE '%' + @test + '%'
boxhead — 2011-03-21T09:27:22-04:00 — #10
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
r937 — 2011-03-21T09:36:19-04:00 — #11
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
sqlstring = sqlstring + 'AND test LIKE ''%'' + @test + ''%'''
IF @somethingelse IS NOT NULL
sqlstring = sqlstring + 'AND somethingelse LIKE ''%'' + @somethingelse + ''%'''
system — 2011-03-21T09:39:37-04:00 — #12
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.
r937 — 2011-03-21T09:54:31-04:00 — #13
whoa, that's hyperbolic
if i tole you oncet, i tole you a thousand times, don't exaggerate
system — 2011-03-21T15:37:18-04:00 — #14
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.