4

i've often come across situations where i can write the conditions of a t-sql query like this...

DECLARE @FirstName NVARCHAR(500)    
SET @FirstName ='Joe'

SELECT * 
FROM dbo.Customers c
WHERE 
    CASE 
        WHEN  @FirstName <> ''  THEN
            CASE WHEN c.FirstName= @FirstName THEN 1 ELSE 0 END
        ELSE 1
     END = 1

Or like this... 

SELECT * 
FROM dbo.Customers c
WHERE 
    (@FirstName = ''  OR (@FirstName <> '' AND c.FirstName = @FirstName))

They both produce the same results and both query plans appears to be the same.

So i'm curious as to which one is best practice or if there are any pitfalls with one over the other?

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
MakkyNZ
  • 2,215
  • 5
  • 33
  • 53
  • 2
    Best practice is to solve customer problems instead of worrying about equally valid SQL syntaxes – Andomar Jul 30 '12 at 15:43
  • First worry about clearly expressing the intent of the logic. – HABO Jul 30 '12 at 15:56
  • 3
    Best practice is to use neither. Or possibly the second one in conjunction with `OPTION (RECOMPILE)`. Neither will use an index on `c.FirstName` even when you are looking up a specific value. See [Dynamic Search Conditions in T-SQL](http://www.sommarskog.se/dyn-search.html) and [Catch-all queries](http://sqlinthewild.co.za/index.php/2009/03/19/catch-all-queries/) for a couple of articles on the subject. – Martin Smith Jul 30 '12 at 16:16
  • IMO, I like to use the second one in the `WHERE` clause and the `CASE` only in the `SELECT` part. It seems more readable :) – aF. Jul 30 '12 at 16:05

3 Answers3

3

I think it's also about readability mostly in this case, but you are doing more in the first query example. You're forcing the database to create a value per row of 0 or 1 and requiring the database to check if the value is 0 or 1. The fewer steps the better when creating queries in my opinion.

I would opt for the second approach although you can probably get rid of @FirstName<> '' since it is unnecessary, so your query cleans up a bit more: 

SELECT * 
FROM dbo.Customers c
WHERE 
    (@FirstName = ''  OR c.FirstName = @FirstName)

EDIT: Martin Smith provided a very good insight that you might want to consider.

Shan Plourde
  • 8,528
  • 2
  • 29
  • 42
  • +1 (I reverse the order of those two conditions, so that c.FirstName gets referenced first.) I like that you wrapped this in parens, because it makes it clear that this approach can be extended to allow for other "optionally supplied" search parameters e.g. `AND (c.LastName = @LastName OR @LastName IS NULL)`. (Actually, we use NULL as the "no argument" value, but it works the same. Bottom line is that we don't have to dynamically build a SQL statement based on a variety of supplied arguments. We just pass in the supplied argument values or NULL. – spencer7593 Jul 30 '12 at 18:33
  • Interesting, I didn't realize that leaving out @FirstName<> '' would produce a different result. Doesn't comparing NULL <> '' evaluate to false? Which is what I assume c.FirstName = NULL would always evaluate to. I wonder what rows are getting returned without it. I'll revise my answer. Thanks. – Shan Plourde Jul 30 '12 at 19:28
  • I apologize, I was wrong about your query returning different results; Your statement is equivalent. You are correct that the `@FirstName <> ''` check is redundant. (In Oracle, an empty string is equivalent to NULL, but that's not the case in SQL Server or MySQL). (I removed my previous comment - I don't know what I was thinking; I will run a test case this evening.) – spencer7593 Jul 30 '12 at 19:41
  • Thanks for the follow up. You never know though, I think that the behaviour could be diff. under some circumstances, like a non-standard (and highly not-recommended) ansi_nulls database-wide setting. – Shan Plourde Jul 30 '12 at 19:49
  • that @FirstName <> '' is there so that and second condition can be added without having to alter the first condition. e.g `WHERE (@FirstName = '' OR (@FirstName <> '' AND c.FirstName = @FirstName)) OR (@LastName = '' OR (@LastName <> '' AND c.LastName = @LastName ))` which i know is kinda over-engineering it – MakkyNZ Jul 31 '12 at 08:39
0

I prefer a variant of the second--defaulting your variable to null, because there is a specific optimization in recent SQL Server builds that can optimize for it. 

DECLARE @FirstName NVARCHAR(500) = NULL

SELECT * 
FROM dbo.Customers c
WHERE 
    (@FirstName IS NULL OR c.FirstName = @FirstName)

See this SO answer for more details: https://stackoverflow.com/a/3415629/1564603

Community
  • 1
  • 1
pikewood
  • 1
0

What about using LIKE to avoid Null Mathing ?

DECLARE @FirstName NVARCHAR(500) = NULL

SELECT * FROM dbo.Customers c WHERE (c.FirstName LIKE @FirstName+'%')

I think the results could be the same, even more interesting Regardless of the fact that the cost in time of the search is longer

Argenis Ascanio
  • 1