sql - 条件 WHERE 语句 SQL Server

Question

我想创建一个将返回所有 Country 的 SP行除非 CountryID作为参数提供。这是我想象的它可能会起作用的方式，但它不喜欢它。

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

谢谢

附言我认为可能有比简单地根据上述条件重复整个 SELECT 语句更好的方法。

Answer 1

试试这个，它很优雅:)

 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