CODEWITHSUNDEEP
sqlsql-servert-sql
user971741
user971741

Reputation:

A keyword to specify in WHERE clause to get all the data without filtering the field

Let's say I have screen where user select different options to sort data in a grid given below the data is fetched asynchronously through jQuery from database.

When user selects he needs to view user data where status=1

SELECT * FROM user WHERE status=1;

And same for 2, 3, 4 a variable can be passed.

But when user doesn't select anything than it should return results of all the users. So is an expression or keyword that I can pass in the query that will get ALL the record, something like:

SELECT * FROM user WHERE status=ANYTHING; -- EVERYTHING, ALL, ANY

I don't want to write a different query for each and every case, that would not be efficient and cases are also high in number as the above example is just an hypothetical example to explain my situation the real case is bit complex.

Upvotes: 0

Views: 560

Answers (4)

jagruti
jagruti

Reputation: 390

You can use this Query:

SELECT * FROM user WHERE status!='' or status != null
OR
SELECT * FROM user WHERE status IN (1,2,3) // all status values

Upvotes: 0

Hugh Jones
Hugh Jones

Reputation: 2694

You could provide a second parameter to the query so your where clause reads

WHERE STATUS = @Status or @RetrieveAll=1

Or perhaps

WHERE Coalesce(STATUS, 0) = @Status or @RetrieveAll=1

Upvotes: 0

Upendra Chaudhari
Upendra Chaudhari

Reputation: 6543

Use ISNULL() or COALESCE() function. Pass NULL value in parameter for all records and write query like 

SELECT * FROM user WHERE status=ISNULL(@status,status)

UPDATE : Above query will not returns null records, so if your table have null records then you need to write query as Kaf mentioned like 

SELECT * FROM user WHERE @status is null Or status = @status

Upvotes: 1

Kaf
Kaf

Reputation: 33839

SELECT * FROM user WHERE @status is null Or status = @status

Upvotes: 3

Related Questions