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.

Answer 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

Answer 2

Easy enough to wrap up in a single WHERE clause:

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