Conditional WHERE statement SQL Server

问题:

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.

回答1:

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

回答2:

Easy enough to wrap up in a single WHERE clause:

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

回答3:

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

回答4:

Something like this?

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