CODEWITHSUNDEEP
sqlsql-serverdynamic-sql
user2112420
user2112420

Reputation: 985

SQL Server alternative to dynamic query

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.

Upvotes: 2

Views: 1689

Answers (5)

Jozef Babinsky
Jozef Babinsky

Reputation: 140

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.

Upvotes: 2

Rameshkan B
Rameshkan B

Reputation: 88

You can try this

AND ((ISNULL(@Mail,'') = '' or mail = @mail) OR (ISNULL(@Phone,'') = '' or Phone like '%' + @Phone))

Upvotes: 0

Alexei - check Codidact
Alexei - check Codidact

Reputation: 23078

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.

Upvotes: 0

Mitesh Prajapati
Mitesh Prajapati

Reputation: 53

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

Upvotes: -1

Gordon Linoff
Gordon Linoff

Reputation: 1269753

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)

Upvotes: 1

Related Questions