T-SQL: WHERE Clause - How to adjust based on input variable

Question

Using SQL Server 2008. I have an input param in my stored proc called '@State'. The param can basically be '--All--' or can contain the state to filter.

So, if it is '--All--' I don't want to incorporate the @State into the where clause. Otherwise I'd like it to filter based on the provided @State. So basically it could result in this....

SELECT * FROM MyTable
WHERE Type='AAA' AND Status=@Status

or, if they pass '--All--'

SELECT * FROM MyTable
WHERE Type='AAA'

How can I do this in a stored proc?

Marc · Accepted Answer · 2014-03-19 17:35:19Z

3

SELECT
    *
FROM
    MyTable
WHERE
    Type='AAA'
    AND Status = CASE @Status WHEN '--All--' THEN Status ELSE @Status END

answered Mar 19, 2014 at 17:35

Marc

1,0101 gold badge10 silver badges18 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Jian Huang · Accepted Answer · 2014-03-19 17:41:49Z

1

I thought you made a typo. It should be @State, not @Status. This simple query might not be what you are looking for since you want to two sql statements in your requirement.

SELECT * FROM MyTable
WHERE Type='AAA' AND (@State='--All--' or State=@State)

edited Mar 19, 2014 at 17:41

answered Mar 19, 2014 at 17:36

Jian Huang

1,1958 silver badges17 bronze badges

Comments

Tiny Haitian · Accepted Answer · 2014-03-19 17:44:39Z

0

Or...

you could make it even simpler than that:

 SELECT * FROM MyTable
 WHERE Type='AAA' AND @State in ('--All--', State)

No need to do it in a stored procedure, unless absolutely necessary (or if business/coding practice requires you to do so).

answered Mar 19, 2014 at 17:44

Tiny Haitian

4792 silver badges10 bronze badges

Collectives™ on Stack Overflow

T-SQL: WHERE Clause - How to adjust based on input variable

3 Answers 3

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related