-2

I am using a query which its searching inside a table for two conditions. I will give an example:

Select * 
from Customers 
where mobile= '" + textboxt1.Text + "' or Phone = '" + textboxt1.Text + "'

The query returns me the first row which mobile or phone numbers is equal with my textbox1.Text.

I need to set a condition: start looking "entire table" for find the first 'or' statement(mobile). If there is not exist any result then go and search again entire table using the second or condition (Phone).

Is there any easy way which i can write my query? Or do I need to use a case for this?

GMB
  • 216,147
  • 25
  • 84
  • 135
DmO
  • 357
  • 2
  • 14
  • 5
    Please please please parametrise your SQL! Stop injected values! Also, you really need to name your objects. `textboxt1` and`textboxt2` is going to be meaningless to anyone (including you) when you review your application in more than a week's time. [Little Bobby Tables](https://xkcd.com/327/) – Thom A Nov 13 '19 at 22:19
  • 4
    On a different note, SQL Server 2008 is now completely unsupported. You should really be looking at upgrade paths as soon as possible. – Thom A Nov 13 '19 at 22:20
  • I'd suggest something like `SELECT TOP 1 * FROM (Select 'a' as Bob, * from Customers where mobile= @Value UNION ALL Select 'b' as Bob, * from Customers where phone = @Value) ORDER BY Bob`. This will give you the mobile matches earlier than the phone matches. – mjwills Nov 13 '19 at 22:44
  • 1
    @Shmeeku: The question is tagged with C#, so this code makes far more sense in that language than it does in T-SQL. – Sam Axe Nov 13 '19 at 23:06
  • "Unsupported" doesn't convey the full weight of things. Sql Server 2008 is now **end of life**. That means no new updates are issued at all... _not even critical security patches_. Understanding this helps make it clearer why it is _dangerous and irresponsible_ to continue using this older version. – Joel Coehoorn Nov 14 '19 at 14:35

2 Answers2

3

Assuming that not more than one record in the table would match on mobile, and that not more than one record would match on the phone (which seems relevant given your description of your use case), you could use top 1 and conditional ordering:

select top 1 * 
from Customers 
where mobile= @textboxt1 or phone = @textboxt2
order by case when mobile= @textboxt1 then 0 else 1 end

If a record matches on mobile, the conditional ordering clause will put it in first position, and top 1 will eliminate the (possible) other record matching on phone. Else, the (only) matching record on phone will be retained.

Note: don't trust user input. Use prepared statement and query parameters at all times. I modified the query so it uses parameters (@textboxt1, @textboxt2).

GMB
  • 216,147
  • 25
  • 84
  • 135
0

You can sometimes improve performance by choosing a UNION instead of an OR.

SELECT TOP 1 * FROM (
    SELECT *, 0 Ordinal
    FROM Customers
    WHERE mobile = @number

    UNION ALL

    SELECT *, 1 Ordinal
    FROM Customers
    WHERE phone = @number  
) t
ORDER BY Ordinal
Joel Coehoorn
  • 399,467
  • 113
  • 570
  • 794