Link to home
Start Free TrialLog in
Avatar of phunchak
phunchak

asked on

Using parm in where clause OR

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

Avatar of Anthony Perkins
Anthony Perkins
Flag of United States of America image

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)
ASKER CERTIFIED SOLUTION
Avatar of Anthony Perkins
Anthony Perkins
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
And of course @@Buisness_ID should be @Business_ID.

Sorry for the multiple posts.