2

I would like to know what options we have to do not make dynamic queries.

For example:

IF @Mail <> ''
BEGIN
    SELECT @Where = @Where + ' AND Mail =  @Mail '
END
ELSE IF @Phone <> ''
BEGIN
    SELECT @Where = @Where + ' AND Phone like ''%'' + @Phone '
END

I would like not do do this, I would like to avoid dynamic queries, if someone can help me.

By the way I want to filter by Mail, but if Mail does not exist then I have to filter by Phone, but never by both.

Alexei - check Codidact
  • 22,016
  • 16
  • 145
  • 164
user2112420
  • 955
  • 5
  • 11
  • 26
  • (1) I think the `phone` logic is incorrect because the expression does not include `@Phone`. (2) I added the SQL Server tag because the syntax looks like SQL Server. – Gordon Linoff Feb 18 '16 at 12:04
  • There's nothing wrong with dynamic sql apart from readability. Can you not build your dynamic sql in code rather than a stored proc. Consider using an ORM? – Mark Feb 18 '16 at 15:49

5 Answers5

2

If you are going for performance, the best solution is to make separate queries for each of the cases.

Gordon's solution is fine, but SQL server will not use any indexes you may want to use on the columns you filter by. It can be enhanced to use indexes by adding OPTION(RECOMPILE), but this will cause to recompile the query each time it is run. It will considerably improve performance if your table has a lot of rows (and you define indexes on the columns), but decrease performance for table with few rows (or without indexes) - it will have basically the same performance as dynamic query.

Jozef Babinsky
  • 140
  • 9
1

You are probably better off with a dynamic query, in terms of performance. Simpler queries are typically easier to optimize.

You can however phrase the where clause as:

where . . . AND
      (@Mail = '' or mail = @mail) AND
      (@Phone = '' or Phone like '%' + @Phone)

Note: It is quite common for the NULL value to be used to indicate "all". So a more typical formulation is:

WHERE . . . AND
      (@Mail IS NULL or mail = @mail) AND
      (@Phone IS NULL or Phone like '%' + @Phone)
Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786
  • What happen if mail and phone are not null, will make the where with both or just with mail? First I want to filter by Mail, but if is null the make the filter by the phone, but never with both. thanks – user2112420 Feb 18 '16 at 12:08
0

For multiple filters it is a great chance that dynamic SQL will behave better, as ORs and performance usually do not get along in SQL queries.

However, if filters are NULL on "no filter value", the query can be written like this:

SELECT Mail, Phone, <other columns here>
FROM table
WHERE Mail = COALESCE(@Mail, Mail)
   AND Phone LIKE (COALESCE('%' + @Phone, Phone)

All '' values can easily be converted into NULLs using nullif(@Mail, ''). Also, this will treat '' AND NULL homogeneously.

Alexei - check Codidact
  • 22,016
  • 16
  • 145
  • 164
  • But my question is, if Mail and Phone are not null, the query will select just by Email or also by Phone? Thanks – user2112420 Feb 18 '16 at 12:30
  • The query should work for all combinations: both are null, only one is null, both are not null. Also, it should scale to any number of filters, although the performance might degrade quite a bit. – Alexei - check Codidact Feb 18 '16 at 12:55
  • The only down side to the isnull approach is that it creates terrible query plans. – Mark Feb 18 '16 at 15:47
  • @Mark - That is correct. That's why I would directly go with dynamic query. Or even better, use LINQ (dynamic linq) or something similar to generate the queries. – Alexei - check Codidact Feb 18 '16 at 17:21
0

You can try this

AND ((ISNULL(@Mail,'') = '' or mail = @mail) OR (ISNULL(@Phone,'') = '' or Phone like '%' + @Phone))
Rameshkan B
  • 78
  • 9
-1

Why don't you use CASE WHEN.

Here is the URL which can help you to make this query.

"CASE" statement within "WHERE" clause in SQL Server 2008

Community
  • 1
  • 1
Mitesh Prajapati
  • 53
  • 1
  • 10