?
Solved

Using parm in where clause OR

Posted on 2005-05-09
3
Medium Priority
?
208 Views
Last Modified: 2010-03-19
I want to have a parameter that can be either passed in or null.  If it is passed in I want to use it in the where clause to filter the results.  But if it is not passed in I want to show all the business ID's in the Customer table that are joined to the Business Table AND all the Customers that have null Business_Id's


@Business_ID  INT= NULL

Select * FROM CUSTOMER
OUTER JOIN BUSINESS
on Customer.CUSTOMER.Business_ID = BUSINESS.Business_ID
WHERE
CUSTOMER.Business_ID =
CASE
@Business_ID is NOT NULL  --- A parm is passed in
THEN
       @BUSINESS_ID --- use the value of the parm
ELSE
       BUSINESS.Business_ID OR NULL -- problem here
END


I can get most of this to work except for the situation where the Customer Business_ID is Null

0
Comment
Question by:phunchak
  • 3
3 Comments
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 13964233
Try it like this:

Select *
FROM CUSTOMER
          OUTER JOIN BUSINESS on Customer.CUSTOMER.Business_ID = BUSINESS.Business_ID
WHERE (@@Business_ID Is Null Or CUSTOMER.Business_ID = @Business_ID)
0
 
LVL 75

Accepted Solution

by:
Anthony Perkins earned 1500 total points
ID: 13964241
Let's try that again:

Select *
FROM CUSTOMER
          INNER JOIN BUSINESS on Customer.Business_ID = BUSINESS.Business_ID
WHERE (@@Business_ID Is Null Or CUSTOMER.Business_ID = @Business_ID)

Or:
Select *
FROM CUSTOMER
          LEFT JOIN BUSINESS on Customer.Business_ID = BUSINESS.Business_ID
WHERE (@@Business_ID Is Null Or CUSTOMER.Business_ID = @Business_ID)
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 13964245
And of course @@Buisness_ID should be @Business_ID.

Sorry for the multiple posts.
0

Featured Post

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

Question has a verified solution.

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

In this article we will learn how to fix  “Cannot install SQL Server 2014 Service Pack 2: Unable to install windows installer msi file” error ?
In part one, we reviewed the prerequisites required for installing SQL Server vNext. In this part we will explore how to install Microsoft's SQL Server on Ubuntu 16.04.
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…
Suggested Courses

830 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