asked on

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?

lisa_mc

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)

lisa_mc

sorry I can't really tell you about SSRS as Im not very familiar with it

ASP8MBM

Have you tried this?
SELECT *
FROM Orders
WHERE ((SiteID = @paramSiteID) OR (ClientName LIKE '%'+@paramClientName+'%') )

ASKER CERTIFIED SOLUTION

lisa_mc

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Alpesh Patel

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

Mike McCracken

This question has been classified as abandoned and is closed as part of the Cleanup Program. See the recommendation for more details.