multi criteria selection - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

multi criteria selection

Author

Topic

archomage
Starting Member

2 Posts

Posted - 2006-07-07 : 04:34:59

hy
It may be a simple problem, but I don't know how to solv it in sql server.
I have a VB / SqlServer 2005 application. I have a search form that the user can search and filter for records.
I use stored procedures for performance.
the problem is that some combo boxes from the application can be unselected, that is null for the stored procedure's parameters.

Is there an sql manner in which I could resolve the problem?

Here is what I have tried:

select 'Nr Data ' =convert(varchar,Nr)+'-'+Convert(varchar,Data,103) ,
Contract,Suma,NumeAgent,
d.Id as 'Id agent',
e.Denumire as 'Canal',
e.Canal as 'Id canal'
from dbo.CONTRACTE a ,dbo.CONTRACTE_DETALII b ,
dbo.CORESPONDENTA c , dbo.AGENTI_CONTRACTE d,
dbo.CANAL e
where
a.Id=b.Id
and a.Id=c.Id
and Data >=convert(datetime,@DataStart,103)
and Data <=convert(datetime,@DataEnd,103)
and b.IdAgent=d.Id
and e.Canal=a.Canal
and (@canal =null or e.Canal=@canal)
and (@idAgent = null or d.Id=@idAgent)
order by NumeAgent

Any ideas?
Thank you for your time

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2006-07-07 : 04:50:42

Your problem is you are comparing @canal and @idAgent with NULL using = operator which will never give you desired result.

Change it to:

and (@canal Is null or e.Canal=@canal)
and (@idAgent Is null or d.Id=@idAgent)

In fact wherever you compare with NULL, use Is Null or Is Not Null.

Harsh Athalye
India.
"Nothing is Impossible"

jen
Master Smack Fu Yak Hacker

4110 Posts

Posted - 2006-07-07 : 04:51:51

i got a bit confused with your query but here is something to start out with...

create procedure spFilter(
@param1 varchar(10)='mydefaultcriteria',
@param2 varchar(10)=null,
@param3 varchar(10)=null
as

select t1.field1,t2.field1...
from table1 t1
inner join on table2 t2
where t1.field1=coalesce(@param1,t1.field1)
and t2.field1=coalesce(@param2,t2.field1)
....

the key here is to set a default value for the parameter if you do have one and/or use coalesce to handle the null values for the parameters, the query will provide you either with a specific parameter value or all if null is specified

hope this helps...

--------------------
keeping it simple...

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-07-07 : 04:53:21

[code]select 'Nr Data ' = convert(varchar,Nr) + '-' + Convert(varchar, Data, 103),
Contract, Suma, NumeAgent,
d.Id as 'Id agent',
e.Denumire as 'Canal',
e.Canal as 'Id canal'
from dbo.CONTRACTE a inner join dbo.CONTRACTE_DETALII b
on a.Id = b.Id
inner join dbo.CORESPONDENTA c
on a.Id = c.Id
inner join dbo.AGENTI_CONTRACTE d
on b.IdAgent = d.Id
inner join dbo.CANAL e
on a.Canal = e.Canal
where Data >= dateadd(day, 0, datediff(day, 0, @DataStart), 0)
and Data <= dateadd(day, 0, datediff(day, 0, @DataEnd), 0)
and a.Canal = coalesce(@canal, a.Canal)
and a.Id = coalesce(@idAgent, a.Id)
order by NumeAgent[/code]

Do you allow NULL in @DataStart & @DataEnd ?

archomage
Starting Member

2 Posts

Posted - 2006-07-07 : 05:27:56

it trully helped. Thank you very much !

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources