Need help in dynamic query in where clause

Question

I am new to dynamic sql just learning but I need a dynamic query. I learned somehow from the google and how to frame one but I am stuck at something it may be a very simple thing as I a new to this. I am making an query in which there is use of where clause. My problem is that there can be a filter or there cannot be any filter.

I can have conditions in where clause which can be null sometimes or those conditions would have some values.

I have written a small code but I am not able to get if the value in filter is 0 i.e. there is no filter for that particular column so what to I write as there will be more filter and there will be AND between them so how to I form that query

My code is-

alter procedure db.test_1(@param1 varchar(10),@param2 varchar(10),@param3 varchar(10),@param4 varchar(10)@param5 varchar(10))
as
begin
declare @id varchar(250)
declare @type varchar(250) 
declare @xyz varchar(250)
declare @name varchar(250)
declare @abc varchar(250)
declare @sql varchar(800)
set @id= case when convert(varchar,@param1)='0' then ''
                      when @param1 like '%%' then 'carrier_id in ('+convert(varchar,@param)+')'
                      else 'id='+@param1 end ;
set @type= case when convert(varchar,@param2)='0' then ''
                      else '@type='+@param2 end ;

set @xyz= case when convert(varchar,@param3)='0' then ''
                      else '@xyz='+@param3 end ;

set @name= case when convert(varchar,@param4)='0' then ''
                      else '@name='+@param4 end ;

set @abc= case when convert(varchar,@param5)='0' then ''
                      else '@abc='+@param5 end ;

set @sql='select sum(column_1) from db.test_1 where '+@id+'and'+@type+'and'+@xyz+'and'+@name+'and'+ @abc

select @sql; -- to know what query is running
--execute(@sql)
end

Now for the query if we have 0 for one parameter then it will be null but there will be an extra end in that query how I will do that. Also this is a sample query in my original query I have four joins and then this where clause. I am not able to find a way

Any help is welcomed and if you don't understand please just comment. Thanks!

Answer 1

Instead of '' when there is no filter use comparison to the field itself, i.e.

set @id= case when convert(varchar,@param1)='0' then 'id=id'
                  when @param1 like '%%' then 'carrier_id in ('+convert(varchar,@param)+')'
                  else 'id='+@param1 end ;

That way it will always be true and will not break the rest of the query. It is pretty inefficient though, so another thing you could try is a variable that keeps track if any filter has already been anything but 0 and in this case add the 'and' to the variable text rather than fixed at the end.

Edit: the 1=1 suggested in another answer here is better as far as efficiency than the comparison to the field itself.

Answer 2

here is a very commonly-used trick for doing exactly this.

The key elements are using "1=1" as a placeholder, and including the " AND " in the filter strings.

alter procedure db.test_1(@param1 varchar(10),@param2 varchar(10),@param3 varchar(10),@param4 varchar(10)@param5 varchar(10))
as
begin
declare @id varchar(250)
declare @type varchar(250) 
declare @xyz varchar(250)
declare @name varchar(250)
declare @abc varchar(250)
declare @sql varchar(800)
set @id= case when convert(varchar,@param1)='0' then ''
                      when @param1 like '%%' then ' and carrier_id in ('+convert(varchar,@param)+')'
                      else ' and id='+@param1 end ;
set @type= case when convert(varchar,@param2)='0' then ''
                      else ' and type='+@param2 end ;

set @xyz= case when convert(varchar,@param3)='0' then ''
                      else ' and xyz='+@param3 end ;

set @name= case when convert(varchar,@param4)='0' then ''
                      else ' and name='+@param4 end ;

set @abc= case when convert(varchar,@param5)='0' then ''
                      else ' and abc='+@param5 end ;

set @sql='select sum(column_1) from db.test_1 where 1=1'+@id+@type+@xyz+@name+ @abc

select @sql; -- to know what query is running
--execute(@sql)
end