MSSQL-如果第一次返回0,则运行第二次选择

假设您的问题是第一个查询返回的值,而不是行数。

WITH t1 AS (
      SELECT COUNT(1) as cnt
      FROM Product P JOIN
           Location PC
           ON P.ProductId = PC.ProductId JOIN
           Users USR
           ON USR.CountryId = PC.CountryId
      WHERE P.Model = @Model AND USR.Id = @UserId
     )
SELECT (CASE WHEN cnt > 0 THEN cnt
             ELSE (SELECT COUNT(1)
                   FROM Product P JOIN
                        Location PC
                        ON P.ProductId = PC.ProductId
                   WHERE P.Model = @Model AND PC.CountryId = @CountryId
        END) as cnt
FROM t

您可以尝试声明一个变量 @Cnt 获取查询金额并签入值。

@Model varchar(50),
@UserId nvarchar(128),
@CountryId int,
@Cnt INT

SELECT
    @Cnt = COUNT(1)
FROM
    Product P
    JOIN Location PC ON P.ProductId = PC.ProductId
    JOIN Users USR ON USR.CountryId = PC.CountryId
WHERE
    P.Model = @Model AND USR.Id = @UserId

if (@Cnt == 0)
BEGIN
    SELECT
        @Cnt = COUNT(1)
    FROM
        Product P
        JOIN Location PC ON P.ProductId = PC.ProductId
    WHERE
        P.Model = @Model AND PC.CountryId = @CountryId
END

SELECT @Cnt

使用该select语句将无法获得@@rowcount。见 this similar answer 为了澄清。

SELECT @Count=COUNT(1)