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!

You have two major issues here. The first and most important is that your procedure is vulnerable to sql injection. You need to parameterize your dynamic sql. Second is performance. This is a catch-all type of query and there are some things you should do to help improve the performance. Gail Shaw has an excellent article that addresses both of these issues in one fell swoop. sqlinthewild.co.za/index.php/2009/03/19/catch-all-queries — Sean Lange
– Sean Lange, Commented Oct 16, 2015 at 15:32

Tab Alleman · Accepted Answer · 2015-10-16 15:28:00Z

1

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

answered Oct 16, 2015 at 15:28

Tab Alleman

31.8k7 gold badges41 silver badges54 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Aditya Shrivastava Over a year ago

Sir, for case when @param1 like '%%' then' ' and carrier_id in ('+convert(varchar,@param)+')'' in when I am passing parameter as '525,516' this case is not running. The else part is running for the query. But this case is only for sending multiple parameters. Can you help?

Tab Alleman Over a year ago

The logic seems wrong. Perhaps you should make the first case when @param1 like '%,%' then ' and carrier_id in ('+convert(varchar,@param1)+')'

SunKnight0 · Accepted Answer · 2015-10-16 15:28:56Z

0

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.

edited Oct 16, 2015 at 15:28

answered Oct 16, 2015 at 15:23

SunKnight0

3,3611 gold badge13 silver badges8 bronze badges

2 Comments

Aditya Shrivastava Over a year ago

Sir, for case when @param1 like '%%' then 'carrier_id in when I am passing parameter as '525,516' this case is not running. The else part is running for the query. But this case is only for sending multiple parameters. Can you help?

SunKnight0 Over a year ago

You are missing a 1 in param: when @param1 like '%%' then 'carrier_id in ('+convert(varchar,@param1)+')'

Collectives™ on Stack Overflow

Need help in dynamic query in where clause

2 Answers 2

2 Comments

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related