September 25, 2009 at 3:16 pm
Good evening
I would like to take your opinion about this query that I am trying to build
I have a Activity table called tbl_activities and i am trying to do the sorting and filtering ( i don't want to use dynamic query)
the tbl_activities contains 2 fields , Price decimal(18,2) and Activity_Category_Name Nvarchar(50)
what I want to do is to build a stored procedure that do the filter depending on user criteria
so the user will decide the @filter , @filter_v,@key and @key_v
this is the query
declare @Filter_v as nvarchar(10)
set @filter_v = 'Like'
declare @Filter as nvarchar(10)
set @filter = '<=' declare @key as decimal(18,2)
set @key =3
declare @key_v as nvarchar(20)
set @key_v = 'b'
select * from tbl_activities
where
(@key is null
or (case @filter
when '<='
then Price end <= @key
or case @filter
when '<'
then Price end < @key ))
and
( @key_v is null or
case @filter_v
when 'like'
then Activity_Category_Name end like '%'+@key_v+'%' )
The query work so fine and the result also, so my question is, is there is a better way to do that ( question of performance)?
thanks a lot for your Reply.
regards
Wael
September 25, 2009 at 7:12 pm
I think you may come across various sorts of performance issues if you have more data. Sometimes it is better to create a dynamic SQL, fit it with parameters and execute it. The code that you have may be quite hard to optimize by query optimizer.
Regards
Piotr
...and your only reply is slàinte mhath
September 28, 2009 at 2:18 pm
imo this is better (atleast easier to understand at a glance), performance should be the same if not better
select *
from tbl_activities
where
(
@key is null
or (@key is not null and @filter='<=' and Price<=@key)
or (@key is not null and @filter='<' and Price<@key)
)
and
(
@key_v is null
or (@key_v is not null and Activity_Category_Name like '%'+@key_v+'%')
)
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply