Search with several parameters in stored procedure that may be null

Question

I created a searcher as follows :

create procedure [dbo].[search_pazhoheshgar]
    @se_code_melli varchar(10) = NULL,
    @se_name nvarchar(30) = NULL,
    @se_family nvarchar(30) = NULL,
    @se_name_uni nvarchar(100) = NULL,
    @se_name_reshte_tahsili nvarchar(50) = NULL
as
begin try
begin tran
    set nocount on;

    select 
        sabt.code_melli, sabt.name, sabt.family, 
        sabt_como_univercity.name_uni,
        sabt_como_reshte.name_reshte_tahsili
    from 
        sabt 
    inner join 
        sabt_como_univercity on sabt.univercity = sabt_como_univercity.id_uni
    inner join 
        sabt_como_reshte on sabt.name_reshte = sabt_como_reshte.id_reshte_tahsili
    where 
        @se_code_melli is not null
        and @se_code_melli <> '' 
        and sabt.code_melli like '%' + @se_code_melli + '%' 
         or @se_name is not null 
        and @se_name <> ''  
        and sabt.name like '%' + @se_name + '%' 
         or @se_family is not null
        and @se_family <> '' 
        and sabt.family like '%' + @se_family + '%' 
         or @se_name_uni is not null 
        and @se_name_uni <> '' 
        and sabt_como_univercity.name_uni like '%' + @se_name_uni + '%' 
         or @se_name_reshte_tahsili is not null
        and @se_name_reshte_tahsili <> '' 
        and sabt_como_reshte.name_reshte_tahsili like '%' + @se_name_reshte_tahsili + '%'

    commit tran
end try
begin catch
    rollback tran
    return -1
end catch

When I search with a parameter, it is not a problem. But when I set several parameters, the last entered parameter is placed as the search criterion. Obviously, this problem is due to the presence of OR. Now if I use AND instead of OR, I can not put a value NULL, because the search is ineffective. What should I do?

[Catch-all Queries](https://sqlinthewild.co.za/index.php/2009/03/19/catch-all-queries/) — Thom A, Sep 04 '18 at 15:02
Also, please don't be afraid to use whitespace when writing a query. it makes it far more readable not only for you, but others as well. — Thom A, Sep 04 '18 at 15:03

score 1 · Accepted Answer · edited Sep 04 '18 at 15:20

1

I believe the logic you want is:

WHERE (@se_code_melli IS NULL OR sabt.code_melli LIKE '%' + @se_code_melli + '%') AND 
      (@se_name IS NULL OR sabt.name like '%' + @se_name + '%') AND
       . . .

That is, the check is that a parameter is NULL OR that the condition matches. Then these conditions are connected by AND.

edited Sep 04 '18 at 15:20

D Stanley

149,601
11
178
240

answered Sep 04 '18 at 15:03

Gordon Linoff

1,242,037
58
646
786

score 1 · Answer 2 · answered Sep 04 '18 at 15:15

I would begin by using parenthesis, as it's not clear what you are trying to achieve. Are you wanting to return all records where at least one of the values is found in a column?

WHERE
(COALESCE(@se_code_melli,'') != '' AND  sabt.code_melli LIKE '%' + @se_code_melli + '%') OR 
(COALESCE(@se_name,'') != '' AND sabt.name like '%' + @se_name + '%') OR  
(COALESCE(@se_family,'') != '' AND sabt.family like '%' + @se_family + '%') OR
(COALESCE(@se_name_uni,'') != '' AND sabt_como_univercity.name_uni like '%' + @se_name_uni + '%') OR
(COALESCE(@se_name_reshte_tahsili,'') != '' AND sabt_como_reshte.name_reshte_tahsili like '%' + @se_name_reshte_tahsili + '%')

+1 for advocating the use of brackets. SQL will interpret and & or in a fixed way, but who has time to learn what that is. Brackets make your intention much clearer. — Black Light, Sep 04 '18 at 20:40

Search with several parameters in stored procedure that may be null

2 Answers2