条件 WHERE 语句 SQL Server [英] Conditional WHERE statement SQL Server

问题描述

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

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

谢谢

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

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

推荐答案

试试这个，很优雅 :)

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

这篇关于条件 WHERE 语句 SQL Server的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！