Hi
I have a search with for 4 parameters in my project my user could choose 1 or 2 or 3 or 4 or 1 and 2 or 1and 3 or.... all of these are 13 model if i want write a query for each item is not good and inteligent what should i do that have only 1 query and 13 model before i write a query and between all of thses parts i use intersect but it don't have result like this:
ALTER PROCEDURE sp_Search
@docdtl nvarchar(50),
@facnum nvarchar(50),
@creditor nvarchar(50),
@debtor nvarchar(50),
@office int
AS
(select * from Document
where DocDetails=@docdtl and Creditor=@creditor and Debtor=@debtor and OfficeID=@office)
INTERSECT
(select * from Document
WHERE DocDetails=@docdtl and FacNum=@facnum and Creditor=@creditor and OfficeID=@office)
INTERSECT
(select * from Document
WHERE DocDetails=@docdtl and FacNum=@facnum and Debtor=@Debtor and OfficeID=@office)
INTERSECT
(select * from Document
WHERE FacNum=@facnum and Creditor=@creditor and Debtor=@debtor and OfficeID=@office)
INTERSECT
(select * from Document
WHERE DocDetails=@docdtl and FacNum=@facnum and OfficeID=@office)
INTERSECT
(select * from Document
WHERE Creditor=@creditor and Debtor=@debtor and OfficeID=@office)
INTERSECT
(select * from Document
WHERE FacNum=@facnum and Creditor=@creditor and OfficeID=@office)
INTERSECT
(select * from Document
WHERE DocDetails=@docdtl and Debtor=@debtor and OfficeID=@office)
INTERSECT
(select * from Document
WHERE DocDetails=@docdtl and OfficeID=@office)
INTERSECT
(select * from Document
WHERE FacNum=@facnum and OfficeID=@office)
INTERSECT
(select * from Document
WHERE Creditor=@creditor and OfficeID=@office)
INTERSECT
(select * from Document
WHERE Debtor=@debtor and OfficeID=@office)
INTERSECT
(select * from Document
where DocDetails=@docdtl and FacNum=@facnum and Creditor=@creditor and Debtor=@debtor and OfficeID=@office)
RETURN
how i can correct it that have results for my search with these parameters?
Loading
SreekanthPosted Aug 13, 2009, 12:36 AM
may be blndr answer !...can we use or..
i hav a table statemaster with these columns ...
declare @c varchar(50)
declare @d varchar(50)
set @a='2'
set @b='kerala'
set @c='sree'
set @d='2007'
select smstatecode,smstate,smemailid,smcreateddate from statemaster
group by smstatecode,smstate,smemailid,smcreateddate
having smstatecode=@a or smstate=@b or smemailid= @c or smcreateddate = @d
MaharajePosted Aug 12, 2009, 11:03 PM
when it work that all of 4 parameters have value but i want when only one of them has value it work and show the results ineed this plz help
SreekanthPosted Aug 12, 2009, 9:25 AM
let the 4 variables be @a,@b,@c,@d...
ur selecting from tbale1...i think if 1 or 2 selcted other 3 and 4 will be null
then select * from table1
where
acolumn=case when @a<>'' then @a else'' end.....
eg:
select * from employee
where empid=case when empid<>'' then 1770 else 691 end....
if ur looking for another method. Have u tried UNION and UNION all..
insted of inersect...