Solved

Using Two Parameters in SQL SELECT Statement

Posted on 2011-09-15
7
319 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
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
How Do You Stack Up Against Your Peers?

With today’s modern enterprise so dependent on digital infrastructures, the impact of major incidents has increased dramatically. Grab the report now to gain insight into how your organization ranks against your peers and learn best-in-class strategies to resolve incidents.

 
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 Do You Stack Up Against Your Peers?

With today’s modern enterprise so dependent on digital infrastructures, the impact of major incidents has increased dramatically. Grab the report now to gain insight into how your organization ranks against your peers and learn best-in-class strategies to resolve incidents.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
MS SSRS scale out 13 70
how to calculate Quintiles in ssrs 3 50
How can I setup ssrs report  as UTF-8 . 7 27
New to SSRS, extremely slow running report. 8 29
A recent questions about how to add SSRS named instances, couldn't find any that talks about SQL server 2008, anyway I decided to help by creating some screen shots. The installation is straightforward, you just pop the SQL server 2008 installati…
It is helpful to note: This is a cosmetic update and is not required, but should help your reports look better for your boss.  This issue has manifested itself in SSRS version 3.0 is where I have seen this behavior in.  And this behavior is only see…
Two types of users will appreciate AOMEI Backupper Pro: 1 - Those with PCIe drives (and haven't found cloning software that works on them). 2 - Those who want a fast clone of their boot drive (no re-boots needed) and it can clone your drive wh…
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …

827 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