-2

There are several ways to check if a parameter IS NULL or has value. One of the best way to check that is like below:

DECLARE @PhoneNo VARCHAR(12) = '12345'

SELECT *
FROM PhoneNumber
WHERE 
    PhoneNo = @PhoneNo OR @PhoneNo IS NULL

The problem is, when I use OR operator it takes 5 seconds or more. However, if I just write 

PhoneNo = @PhoneNo

it takes less than a sec.

The ultimate solution can be use of dynamic query. I prefer not to do that.

Ali Elmi
  • 376
  • 3
  • 9

2 Answers2

0

What do you want to achieve, if the passed in variable is NULL? It is obvious, that returning all rows might take longer than returning just one fitting row.

If you do not want to accept a NULL in this place turn your logic around:

DECLARE @PhoneNo VARCHAR(12)

SELECT *
FROM PhoneNumber
WHERE 
    @PhoneNo IS NOT NULL AND PhoneNo = @PhoneNo
Shnugo
  • 66,100
  • 9
  • 53
  • 114
  • The question is clear. and the code is more clear what I want to do. The input parameter is dynamic. I am entering a PhoneNo like '123' – Ali Elmi Feb 14 '16 at 11:27
  • @AliElmi So, what's wrong with my suggestion? If you pass in NULL it will return nothing. If you pass in something it will return the fitting row(s). Your query from your question would return **everything** in case of NULL... – Shnugo Feb 14 '16 at 11:42
  • In fact, I pass a value as a parameter meaning it has value. However the performance issue will be happen. By the way, thanks for your suggestion. the solution is using OPTION (RECOMPILE) at the end. – Ali Elmi Feb 15 '16 at 11:10
-1

Option 1

You can use the coalesce() function.

ie, coalesce(PhoneNo, @PhoneNo) = @PhoneNo.

This will replace PhoneNo in the database with the variable if it is null.

Option 2

Use a union query as this can be faster than using 'OR' clauses.

Example:

SELECT * FROM PhoneNumber WHERE PhoneNo = @PhoneNo
UNION ALL
SELECT * FROM PhoneNumber WHERE @PhoneNo IS NULL
Pete
  • 59
  • 4
  • It takes 5 seconds again! – Ali Elmi Feb 14 '16 at 11:20
  • Read about "sargable"... – Shnugo Feb 14 '16 at 11:21
  • Do you have indexes on your database? Ultimately 'OR' queries are never ideal. There is another option, I'll put above. – Pete Feb 14 '16 at 11:25
  • I do have non-cluster INDEX – Ali Elmi Feb 14 '16 at 11:26
  • @AliElmi, I took me 3 seconds of research to find this: http://stackoverflow.com/q/799584/5089204 – Shnugo Feb 14 '16 at 11:41
  • It worked! incredible answer! Thanks a lot – Ali Elmi Feb 14 '16 at 11:43
  • @AliElmi, which option works? Option 1 has a problem with sargability and option 2 is not the same as you wrote above. In your own query you do not check for rows having no phone number, but you check for the passed in parameter being null. I'm quite sure, that Pete found a solution per incidence or magic glass bulb :-) – Shnugo Feb 14 '16 at 11:45
  • Sorry Option 2 was a typo! Which may mean the wrong question was asked to start with? :) Sargability is a pain, but optimisers do strange things and I've seen a variety of results leading to verbose code or, if there are no indexes, it makes no difference! Fortunately we run sql 2014 enterprise so everything is in a clustered columnstore ... – Pete Feb 14 '16 at 12:20