tia_kamakshi
asked on
Creating advance search query in MSSQL 2005
Hi,
I am creating query on MSSQL 2005. I have advanced search option in my form.
Where user can select any criteria, based on critera selected the result should come
Please help me how to put where clause in my stored procedure so that selected parameter should only passed to where clause
For example if fromdate and todate is selected then in where clause, it should come something like this
where s.CreationDate > convert(datetime,@fromdate )
and s.CreationDate < convert(datetime,@todate)
I have following parameter
@fromDate DATETIME = NULL
,@toDate DATETIME = NULL
,@paymentType nVARCHAR(10) = NULL
,@paymentStatus nVARCHAR(10) = NULL
,@Order_Code nVARCHAR(50) = NULL
Please advise
I am creating query on MSSQL 2005. I have advanced search option in my form.
Where user can select any criteria, based on critera selected the result should come
Please help me how to put where clause in my stored procedure so that selected parameter should only passed to where clause
For example if fromdate and todate is selected then in where clause, it should come something like this
where s.CreationDate > convert(datetime,@fromdate
and s.CreationDate < convert(datetime,@todate)
I have following parameter
@fromDate DATETIME = NULL
,@toDate DATETIME = NULL
,@paymentType nVARCHAR(10) = NULL
,@paymentStatus nVARCHAR(10) = NULL
,@Order_Code nVARCHAR(50) = NULL
Please advise
Create procedure
@fromDate DATETIME = NULL
,@toDate DATETIME = NULL
,@paymentType nVARCHAR(10) = NULL
,@paymentStatus nVARCHAR(10) = NULL
,@Order_Code nVARCHAR(50) = NULL
as
Select
S.ID,
S.CourseTitle ,
P.Title,
Coalesce(P.FirstName,'')+' '+Coalesce(P.MiddleName,'')+' '+ Coalesce(P.MiddleName,'')+ ', '+ Coalesce(P.FamilyName,'')+ ', '+ Coalesce(C.EMail,'') as [User Name],
C.Country as Country,
C.City,
Coalesce(C.AddressLine1,'') +', '+ Coalesce(C.AddressLine2,'') +', '+ Coalesce(C.POBox,'') +', '+ Coalesce(C.zipCode,'') as [Full Address],
'M:'+Coalesce(C.Mobile,'') +', W:'+Coalesce(C.Telephone,'') +', FX:'+ Coalesce(C.Fax,'') as [Contact Details],
CASE WHEN PD.WithEK = 1 THEN 'Yes' ELSE 'No' END AS [Parents/Guardian with EK?],
PD.StaffNumber,
S.CreationDate as [Registration Date],
Coalesce(S.Currency,'') +' '+ Coalesce(cast(CO.Amount as nvarchar(30)),'') [Payment Amount],
S.PaymentMode as [Payment Mode],
S.PaymentProof as [Payment Proof],
S.OrderCode as [Order ID],
CO.OrderReference as [Order Reference],
CO.orderStatus as [Order Status],
CASE WHEN CO.MaskedCardNumber IS NULL THEN 'NA' ELSE CO.MaskedCardNumber END AS [Masked Card Number],
CASE WHEN CO.CardHolderName IS NULL THEN 'NA' ELSE CO.CardHolderName END AS [Card Holder Name],
CASE WHEN CO.CardBilliingAddress IS NULL THEN 'NA' ELSE CO.CardBilliingAddress END AS [Card Billing Address],
CO.Comments,
CO.CPGAuthCode as [Brass Number],
CO.ModifiedDate,
S.Creationdate
from EACR_Students S
inner join EACR_PersonalDetails P on S.PersonalDetailID = P.ID --and s.CreationDate > convert(datetime,'2013-04-30 00:00:00')
inner join EACR_ParentDetails PD on S.ParentDetailID = PD.ID
inner join EACR_ContactDetails C on S.ContactDetailID=C.ID
inner join EACR_ImportantDetails I on S.ImportantDetailID=I.ID
left join EAC_ConferenceOrder CO on S.OrderCode = CO.OrderCode
where
-- Need to add criteria
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Many Thanks
It worked.
Regards
It worked.
Regards
ASKER
I will try this tomorow morning around after 12 hrs.
I will come back to you.
Regards,