Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 325
  • Last Modified:

Using Two Parameters in SQL SELECT Statement

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
goodmanro
Asked:
goodmanro
1 Solution
 
lisa_mcCommented:
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
 
lisa_mcCommented:
sorry I can't really tell you about SSRS as Im not very familiar with it
0
 
ASP8MBMCommented:
Have you tried this?
SELECT *
FROM Orders
WHERE ((SiteID = @paramSiteID) OR (ClientName LIKE '%'+@paramClientName+'%') )
0
Get expert help—faster!

Need expert help—fast? Use the Help Bell for personalized assistance getting answers to your important questions.

 
lisa_mcCommented:
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
 
Alpesh PatelAssistant ConsultantCommented:
SELECT *
FROM Orders
WHERE (SiteID = @paramSiteID OR ClientName LIKE '''%'+@paramClientName+'%''')
0
 
mlmccCommented:
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

Become an Android App Developer

Ready to kick start your career in 2018? Learn how to build an Android app in January’s Course of the Month and open the door to new opportunities.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now