Conditional WHERE statement SQL Server

Question

I would like to create a SP that will return all Country rows unless a CountryID is provided as a parameter. Here is how I imagined it might work, but it doesn't like it.

ALTER PROCEDURE [dbo].[usp_return_countries]
    @CountryID AS INT = 0
AS
BEGIN
        SELECT *
        FROM Countries
        WHERE Active = 1

        IF @CountryID > 0 BEGIN
            AND @CountryID = CountryID
        END

END

Thank you

P.S. I thought there might be a better way than simply repeating the entire SELECT statement based on the said condition.

Amar Palsapure · Accepted Answer · 2012-02-02 10:38:56Z

10

Try this, it's elegant :)

 ALTER PROCEDURE [dbo].[usp_return_countries]
   @CountryID AS INT = 0
 AS
 BEGIN

    SELECT *
    FROM Countries
    WHERE Active = 1
    AND (@CountryID = 0 OR @CountryID = CountryID)

 END

answered Feb 2, 2012 at 10:38

Amar Palsapure

9,6561 gold badge29 silver badges46 bronze badges

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

1 Comment

David Over a year ago

ah yes, of course. I seen this but got confused that it would match the table CountryID as 0. Perfect, thanks

Matt Hamilton · Accepted Answer · 2012-02-02 10:37:31Z

6

Easy enough to wrap up in a single WHERE clause:

SELECT * 
FROM Countries 
WHERE Active = 1 AND (@CountryID = 0 OR CountryID = @CountryID)

answered Feb 2, 2012 at 10:37

Matt Hamilton

205k61 gold badges395 silver badges321 bronze badges

Comments

aF. · Accepted Answer · 2012-02-02 13:55:37Z

4

Do it like this:

ALTER PROCEDURE [dbo].[usp_return_countries]
    @CountryID AS INT = 0 AS BEGIN

        IF @CountryID > 0 BEGIN
            SELECT *
            FROM Countries
            WHERE Active = 1
            AND @CountryID = CountryID
        END
        ELSE BEGIN
            SELECT *
            FROM Countries
            WHERE Active = 1
        END

END

edited Feb 2, 2012 at 13:55

answered Feb 2, 2012 at 10:34

aF.

67k45 gold badges141 silver badges201 bronze badges

4 Comments

David Over a year ago

So even if you have a massive select statement, you would have to repeat the entire thing for a additional condition?

aF. Over a year ago

@David this is more readable. If you want, you may choose a more elegant way like the other answers state. But remember, you can repeat the select but only one is really made :P

David Over a year ago

I did state I wanted something other than repeating the select statement. Thanks anyway.

onedaywhen Over a year ago

Your approach may not be 'elegant' but it may produce a better plan.

TJ Amas · Accepted Answer · 2012-02-02 10:41:02Z

3

Something like this?

SELECT *
        FROM Countries
        WHERE Active = 1
AND
    (CountryID = @CountryID AND @CountryID <> 0) or (@CountryID = 0)

answered Feb 2, 2012 at 10:41

TJ Amas

1,30113 silver badges19 bronze badges

Collectives™ on Stack Overflow

Conditional WHERE statement SQL Server

4 Answers 4

1 Comment

Comments

4 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

1 Comment

Comments

4 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related