Solved

Creating advance search query in MSSQL 2005

Posted on 2013-05-31
3
343 Views
Last Modified: 2013-06-03
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


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

Open in new window

0
Comment
Question by:tia_kamakshi
  • 2
3 Comments
 
LVL 41

Accepted Solution

by:
ralmada earned 500 total points
ID: 39210550
just do something like

where (@fromDate is null or s.CreationDate > convert(datetime,@fromdate)) and
(@toDate is null or s.CreationDate < convert(datetime, @toDate)) and
(@Order_Code is null or s.Order_Code = @OrderCode) and ...

.... the same with the other parameters
0
 

Author Comment

by:tia_kamakshi
ID: 39213052
Thanks for your help.
I will try this tomorow morning around after 12 hrs.
I will come back to you.

Regards,
0
 

Author Closing Comment

by:tia_kamakshi
ID: 39216904
Many Thanks
It worked.

Regards
0

Featured Post

DevOps Toolchain Recommendations

Read this Gartner Research Note and discover how your IT organization can automate and optimize DevOps processes using a toolchain architecture.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

PL/SQL can be a very powerful tool for working directly with database tables. Being able to loop will allow you to perform more complex operations, but can be a little tricky to write correctly. This article will provide examples of basic loops alon…
In this article I will describe the Backup & Restore method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
Here's a very brief overview of the methods PRTG Network Monitor (https://www.paessler.com/prtg) offers for monitoring bandwidth, to help you decide which methods you´d like to investigate in more detail.  The methods are covered in more detail in o…
This video shows how to remove a single email address from the Outlook 2010 Auto Suggestion memory. NOTE: For Outlook 2016 and 2013 perform the exact same steps. Open a new email: Click the New email button in Outlook. Start typing the address: …

919 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

14 Experts available now in Live!

Get 1:1 Help Now