Solved

Using Two Parameters in SQL SELECT Statement

Posted on 2011-09-15
7
321 Views
Last Modified: 2012-06-27
I am attempting to setup two parameters in a SQL SELECT statement.  I am collecting the values for these parameters via an SSRS report.

SELECT *
FROM Orders
WHERE (SiteID = @paramSiteID OR ClientName LIKE '%'+@paramClientName+'%')

Open in new window


However, when the SiteID is provided, the SELECT statement is still using the LIKE portion of the statement and returning all records in the db.  I am trying to achieve a result where if the SiteID is provided, it returns only that record (WHERE SiteID = @paramSiteID).  Alternatively, if the user types data into the ClientName parameter, I would like to execute the LIKE clause.  I would not like the user to be able to enter data in both fields.

Question:  Is there a way to achieve this in SSRS?  Is there a better way of structuring my query above to achieve the result that I would like?
0
Comment
Question by:goodmanro
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
7 Comments
 
LVL 3

Expert Comment

by:lisa_mc
ID: 36542044
hi

try a case statement in the where clause

SELECT *
FROM Orders
WHERE (case when @paramSiteID is not NULL then SiteID = @paramSiteID
else ClientName LIKE '%'+@paramClientName+'%' end)
0
 
LVL 3

Expert Comment

by:lisa_mc
ID: 36542050
sorry I can't really tell you about SSRS as Im not very familiar with it
0
 
LVL 2

Expert Comment

by:ASP8MBM
ID: 36542058
Have you tried this?
SELECT *
FROM Orders
WHERE ((SiteID = @paramSiteID) OR (ClientName LIKE '%'+@paramClientName+'%') )
0
Business Impact of IT Communications

What are the business impacts of how well businesses communicate during an IT incident? Targeting, speed, and transparency all matter. Find out more in this infographic.

 
LVL 3

Accepted Solution

by:
lisa_mc earned 500 total points
ID: 36542320
hi again

PLease ignore my comment I have go tthe syntax wrong plus Ive been reading thats a very 'ugly' way to do it - plus it doesn't work well

I have noticed that you could do an if else statement which is alot easier to read eg

IF @paramSiteID is not NULL and @paramClientName is NULL THEN
   SELECT * FROM Orders WHERE SiteID = @paramSiteID;
ELSE
   SELECT * FROM Orders WHERE ClientName LIKE '%'+@paramClientName+'%';

instead of the else you may want to use

IF @paramSiteID is NULL and @paramClientName is not NULL THEN
   SELECT * FROM Orders WHERE ClientName LIKE '%'+@paramClientName+'%';

it just depends what error checking you have on stopping the user entering value in both boxes or
entering no values at all

Sorry about the confusion
0
 
LVL 21

Expert Comment

by:Alpesh Patel
ID: 36542337
SELECT *
FROM Orders
WHERE (SiteID = @paramSiteID OR ClientName LIKE '''%'+@paramClientName+'%''')
0
 
LVL 101

Expert Comment

by:mlmcc
ID: 36967117
This question has been classified as abandoned and is closed as part of the Cleanup Program. See the recommendation for more details.
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

Time Corrections for Reports Working with a report, we made some interesting discoveries about the time corrections/updates We are using the following Parameters: Starting Entered Date (Date) formatted as Data type: "Date/Time" Ending Entered …
Have you ever had to extract data from a Microsoft SQL Server database and export it to an Excel file, but did not want to use a DTS package? The concept in this article is not new, but it is the answer and will also work on 64-bit SQL boxes.   …
There are cases when e.g. an IT administrator wants to have full access and view into selected mailboxes on Exchange server, directly from his own email account in Outlook or Outlook Web Access. This proves useful when for example administrator want…
Monitoring a network: why having a policy is the best policy? Michael Kulchisky, MCSE, MCSA, MCP, VTSP, VSP, CCSP outlines the enormous benefits of having a policy-based approach when monitoring medium and large networks. Software utilized in this v…

688 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