CODEWITHSUNDEEP
sql-server-2008
tempid
tempid

Reputation: 8208

sql query - get all records based on flag

I have table in MS SQL 2008 which has a column called Status. It can have "A" or "I" or "P" as values. I have a stored procedure which takes @Status as a parameter. If the parameter is null, I need to retrieve all the records where the status is either A or I or P (basically ignoring the Status field). If the paramenter is only A (for example), I need to get only records which have A as the status. How do I get this done in a single SELECT statement without IF.. ELSE?

 -- This is just a snippet of a larger query. 
 -- Can the following be done without IF ELSE?
    IF @Status = NULL
       BEGIN
         SELECT * FROM Person
       END
    ELSE SELECT * FROM Person WHERE Status = @Status

Edit: My goal is not to duplicate the same logic twice. The select statement has a bunch of joins and stuff, and I don't want to repeat it again in the else condition.

Edit #2: My bad - I checked the table again and it does contain records with NULL status, which I don't want. Here's the table -

Id      Status
--------------
1        A
2        I
3        P
4        NULL

If @Status is "-1" (I'll default the parameter to -1), I want 1, 2, 3 records. How do I write the query?

Upvotes: 1

Views: 4179

Answers (5)

Dmitry Samuylov
Dmitry Samuylov

Reputation: 1564

This should do it:

select * from Person
where (@Status is not null and Status = @Status) or
      (@Status is null and Status in ('A', 'I', 'P'))

Upvotes: 0

Ebrahim Asadi
Ebrahim Asadi

Reputation: 384

declare @status CHAR(1)
set @status = 'A'
select * from Person where (@status IS null) or (@status = status)

When @status = NULL, this query returns all records.

Upvotes: 0

anon
anon

Reputation:

A third option:

SELECT * FROM dbo.Person WHERE Status = COALESCE(@Status, Status);

Or you might prefer ISNULL:

SELECT * FROM dbo.Person WHERE Status = ISNULL(@Status, Status);

And just for completeness, while the selectivity here is highly unlikely to justify it, you may find that the plans for @Status IS NULL and @Status IS NOT NULL vary widely enough that caching a plan with one or the other can lead to poor performance in the opposite case. One way to deal with this is dynamic SQL. So again, with the disclaimer that this is likely a good option in your case, it is a commonly used alternative to these plan-pinning COALESCE/ISNULL/OR options.

DECLARE @sql NVARCHAR(MAX);

SELECT @sql = N'SELECT * FROM dbo.Person' + 
  COALESCE(N' WHERE Status = ''' + CONVERT(VARCHAR(12), @Status) + '''', '');

PRINT @sql;
--EXEC sp_executesql @sql;

And of course you know you shouldn't be using SELECT * in production code, right?

Upvotes: 1

Zeph
Zeph

Reputation: 1728

SELECT * FROM Person WHERE @Status IS NULL OR Status = @Status

Upvotes: 0

Mark Byers
Mark Byers

Reputation: 838186

You can do it using OR:

SELECT * FROM Person WHERE Status = @Status OR @Status IS NULL

Or UNION ALL:

SELECT * FROM Person WHERE Status = @Status
UNION ALL
SELECT * FROM Person WHERE @Status IS NULL

Upvotes: 1

Related Questions