Solved

Using Two Parameters in SQL SELECT Statement

Posted on 2011-09-15
7
320 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
Webinar: Aligning, Automating, Winning

Join Dan Russo, Senior Manager of Operations Intelligence, for an in-depth discussion on how Dealertrack, leading provider of integrated digital solutions for the automotive industry, transformed their DevOps processes to increase collaboration and move with greater velocity.

 
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 100

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

How our DevOps Teams Maximize Uptime

Our Dev teams are like yours. They’re continually cranking out code for new features/bugs fixes, testing, deploying, responding to production monitoring events and more. It’s complex. So, we thought you’d like to see what’s working for us. Read the use case whitepaper.

Question has a verified solution.

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

Suggested Solutions

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.   …
Hi All, I am here to write a simple article to move SSRS (SQL Server Reporting Services) reports from one server to another. When I have faced the same issue to move reports those were developed by developer on development server and now need to …

696 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