CODEWITHSUNDEEP
sqlsql-servert-sql
Jake Manet
Jake Manet

Reputation: 1252

MSSQL - Run second select if first returns 0

I have to run the second select if the first does not return any data but what i tried, it does not work.

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

SELECT
        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 @@rowcount = 0
    SELECT
        COUNT(1)
    FROM
        Product P
        JOIN Location PC ON P.ProductId = PC.ProductId
    WHERE
        P.Model = @Model AND PC.CountryId = @CountryId

The are 2 results... how can i return only one?

Thanks.

Upvotes: 1

Views: 84

Answers (3)

Ross Bush
Ross Bush

Reputation: 15155

You will not get a @@rowcount with that select statement. See this similar answer for clarification.

You can create a variable like below and use it in place of @@rowcount

SELECT @Count=COUNT(1)

Upvotes: 0

D-Shih
D-Shih

Reputation: 46219

You can try to declare a variable @Cnt to get your query amount and check in the value.

@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

Upvotes: 2

Gordon Linoff
Gordon Linoff

Reputation: 1269445

Presumably, your question is about the value returned by the first query, not the number of rows.

If so, you can write this as one query:

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

Upvotes: 2

Related Questions