Maharaje

Maharaje

  • NA
  • 133
  • 0

SearchQuery

Aug 12 2009 7:09 AM

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?

Answers (3)