Solved

Using Two Parameters in SQL SELECT Statement

Posted on 2011-09-15
7
315 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
Top 6 Sources for Identifying Threat Actor TTPs

Understanding your enemy is essential. These six sources will help you identify the most popular threat actor tactics, techniques, and procedures (TTPs).

 
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

Maximize Your Threat Intelligence Reporting

Reporting is one of the most important and least talked about aspects of a world-class threat intelligence program. Here’s how to do it right.

Join & Write a Comment

In this short article I will be talking about two functions in the SQL Server Reporting Services (SSRS) function stack.  Those functions are IIF() and Switch().  And I'll be showing you how easy it is to add an Else part to the Switch function. T…
Introduction: This article is aimed at report developers who are used to developing reports using relational databases and have gotten a first-time assignment to develop reports on OLAP cubes. It demonstrates how to build a report using SQL Ser…
Get a first impression of how PRTG looks and learn how it works.   This video is a short introduction to PRTG, as an initial overview or as a quick start for new PRTG users.
When you create an app prototype with Adobe XD, you can insert system screens -- sharing or Control Center, for example -- with just a few clicks. This video shows you how. You can take the full course on Experts Exchange at http://bit.ly/XDcourse.

708 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

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now