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
@Business_ID INT= NULL
Select * FROM CUSTOMER
OUTER JOIN BUSINESS
on Customer.CUSTOMER.Business
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
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
And of course @@Buisness_ID should be @Business_ID.
Sorry for the multiple posts.
Sorry for the multiple posts.
Select *
FROM CUSTOMER
OUTER JOIN BUSINESS on Customer.CUSTOMER.Business
WHERE (@@Business_ID Is Null Or CUSTOMER.Business_ID = @Business_ID)