How can I reuse subquery in condition checking in SQL Server?

Question

I want to retrieve the results from SQL Server 2012.But for checking condition I rewrote the same subquery.Is there any idea to use the subquery just once and retrieve the result?

My query:

sql = "SELECT customer_id,ISNULL(first_name, '') + ' ' + ISNULL(middle_name, ' ') + ' ' + ISNULL(last_name, ' ') AS 'Customer_name', (ISNULL(city, '') + ',' + ISNULL(district, ' ') + ',' + ISNULL(zone, ' ')) as 'Location' FROM customer_detail WHERE 1=1";

if(location != "")
{
    sql += " AND (ISNULL(city, '') + ',' + ISNULL(district, ' ') + ',' + ISNULL(zone, ' ')) LIKE '%" + location + "%'"";
}

Query after AND is same as above subquery. Thanks in advance.

Please also be very aware that if your local variable `location` is in **any** way user editable either now or in the future (eg. entered on a search form, fetched from a database table, taken as a runtime argument, etc) then you have just created a SQL injection bug - congratulations! Please look at using [parameterized SQL queries](http://stackoverflow.com/questions/7505808/why-do-we-always-prefer-using-parameters-in-sql-statements) instead so that your code looks like this instead: `sql+=" AND (...) LIKE '%' + @location + '%'"";` — pcdev, Mar 21 '17 at 07:41
Another comment on SQL: writing this sort of thing can be slow: `SELECT ... FROM ... WHERE (ISNULL(something,' ') + ...) LIKE '%blah%'`. It will perform very poorly on any database with lots of rows. Two reasons: SQL Server cannot use an index seek because you're applying functions to columns (`ISNULL` as well as concatenating with `+`), secondly using LIKE '%blah%' is much slower than LIKE 'blah%'. Sometimes you need the first option, but be aware that it will be slower. — pcdev, Mar 21 '17 at 07:47

Imre Pühvel · Accepted Answer · 2017-03-21T07:50:34.307

2

It seems you are not looking so much for sub-query than a user defined function (UDF) to merge 3 text column in a prettier way.

If you do not want to use UDF then you could use Common Table Expressions (CTE) to write the expression just once.

Using CTE has also the benefit of encapsulating your reused block right inside query, whereas the UDF would need to be added to your DB before executing your query. This may or may not be a desirable thing depending on reusability needs.

CTE solution would be along the lines of this:

WITH CTE (Id, [Name], [Location]) as 
( 
    SELECT customer_id,
        ISNULL(first_name, '') + ' ' + ISNULL(middle_name, ' ') + ' ' + ISNULL(last_name, ' '),
        ISNULL(city, '') + ',' + ISNULL(district, ' ') + ',' +ISNULL(zone, ' ')
    FROM customer_detail 
)
select * 
from CTE 
where 1=1
    AND [Location] LIKE '%' + @location + '%'

Also, generally you can expect CTE to perform generally better than UDFs as the query optimizer can modify the plan to match specific query needs.

edited Mar 21 '17 at 07:50

answered Mar 21 '17 at 07:34

Imre Pühvel

4,468
1
34
49

Scalar UDFs are notoriously bad for performance, and also prevent paralelism. Avoided at all cost :) – dean Mar 21 '17 at 08:01
I agree UDFs are often an issue. CTE will not have that issue I assume? In the past I have replaced functions by joining in the sub query with an other apply. Would that be quicker or slower? – vikjon0 Mar 21 '17 at 08:57
@vikjon0 Query optimizer works surprisingly well for CTE's in MSSQL, being able to sniff parameters and optimize plan, or even to eliminate execution completely if possible. Note that this can be different for other RDBMS, For example Postgres seems to blindly execute CTE first and then starts to think about the main query, forcing you to prefer apply. In my experience MSSQL is smarter in this regard and lets you use whatever syntax you prefer. – Imre Pühvel Mar 22 '17 at 07:47

score 0 · Answer 2 · answered Mar 21 '17 at 10:55

I would go with a reusable view and stored procedure for the actual search.

    create view vCustomerDetail
    as
        select  [customer_id]
              , isnull([first_name] + ' ', '') + isnull([middle_name] + ' ', '') + isnull([last_name], '') as [Customer_name]
              , isnull([city] + ', ', '') + isnull([district] + ', ', '') + isnull([zone], '') as [Location] 
        from    [customer_detail] 
    go

    create proc pCustomerDetailByLocation
    (
        @location nvarchar(200) = ''
    )
    as
    begin
        set nocount on;

        select  *
        from    [vCustomerDetail]
        where   [Location] = ''

        union all

        select  *
        from    [vCustomerDetail]
        where   [Location] like '%' + @location + '%';
    end
    go

You would call the stored procedure from your code with and pass along the location in the parameter. That way you can prevent SQL injection and also make use of better execution plan caching than with an ad-hoc query.

You can either use a union like I've done in my example, or combine it with an OR statement, like so:

    where   [Location] = '' or [Location] like '%' + @location + '%';

I found the union to be beneficial in some cases over because you're helping the optimizer split up his lookup logic.

How can I reuse subquery in condition checking in SQL Server?

2 Answers2