CODEWITHSUNDEEP
mysqlsql
root beer
root beer

Reputation: 1

how can I use a single sql query to select a single customer or all customers?

I'm working on a report for my company. The report will be able to select a specific customer or all customers.

I have 2 queries

Select *
from customers

and

Select *
from customers
where id = @customer_id

How can I select a specific customer or all customers with a single sql query?

I tried this but it gave me error.

select *
from customers
where id in ( case when @customer_id > 0 Then @customer_id else (select id from customers ) end)

Upvotes: 0

Views: 610

Answers (4)

Dr. X
Dr. X

Reputation: 2930

You can use functions in sql;

 DROP FUNCTION IF EXISTS showCustomer
    CREATE FUNCTION showCustomer (Id int)
    RETURNS TABLE
    AS 
    BEGIN
     IF Id = -1 
       RETURN (SELECT * from customers)
     ELSE 
       RETURN (SELECT * from customers where customers.id=id )
     END
   END

Upvotes: 0

Shaun-Adam
Shaun-Adam

Reputation: 76

I don't have a server installation to test this with at the moment, but I think I have achieved this kind of logic using the following before. Your attempt at using a CASE statement within the where clause isn't valid. Instead I have wrapped the query desired in each case by an outer query which is allowed to use a CASE statement within the selectors (as usual).

Again, I can't test this so some slight modifications may be necessary. 

Declare @customer_id int
Set @customer_id= 5000

Select 
Case When @customer_id > 0 Then (Select customer_id From Customers Where id = @customer_id) 
Else (Select customer_id From Customers) End As 'customer_id'

Upvotes: 0

Khalil
Khalil

Reputation: 1107

This query will get your desired result

select * from customers where @customer_id IS NULL OR id=@customer_id

Upvotes: 0

Blorgbeard
Blorgbeard

Reputation: 103545

You can use a trick like this:

select * from customers where id=@customer_id or @customer_id=0

Upvotes: 1

Related Questions