CODEWITHSUNDEEP
t-sql
EInherjar
EInherjar

Reputation: 339

If @@ROWCOUNT is 0 stored procedure

I have the following code in my stored procedure, which executes upon user input on an HTML page.

SELECT  a.code, a.name
FROM    table2 b
        INNER JOIN table1 a ON a.id = b.id
WHERE   b.data = @parameter
        AND b.condition = 1
        IF @@ROWCOUNT = 0

    SELECT  a.code, a.name
    FROM    table2_archive b
            INNER JOIN table1 a ON a.id = b.id
    WHERE   b.data = @parameter
            AND b.condition = 1

I want to check, if an entry exists in the current table and if not, it should go check in the archives and return that. However, when the procedure is called, it returns both tables, so if there is no data in the current table, the user will get an error, since both tables are returned, but I only want it to return one. What exactly am I doing wrong?

Upvotes: 2

Views: 5691

Answers (2)

Vasily
Vasily

Reputation: 5782

You've wrong understanding of @@ROWCOUNT usage, yes it

returns the number of rows affected by the last statement.

but it doesn't prevent execution of previous query.

If you want to use @@ROWCOUNT then previous statement shouldn't have a output result (e.g. it can be update, insert), and your query can looks like this

--varchar(100) -- because you don't specified the data types

DECLARE @parameter AS VARCHAR(100) = 'xxxx'

DECLARE @tbl1 AS TABLE (code VARCHAR(100), name VARCHAR(100))

INSERT INTO @tbl1 (code, name) -- last statement
SELECT  a.code, a.name
FROM    table2 b
        INNER JOIN table1 a ON a.id = b.id
WHERE   b.data = @parameter
        AND b.condition = 1

-----------------------------------
IF @@ROWCOUNT = 0 -- check if last statement are not produced the rows
--also `IF NOT EXISTS (SELECT * FROM @tbl1)` can be used here, to check if @tbl1 contains a records
    BEGIN
         SELECT  a.code, a.name
         FROM    table2_archive b
                 INNER JOIN table1 a ON a.id = b.id
         WHERE   b.data = @parameter
                 AND b.condition = 1        
    END
ELSE
    SELECT * FROM @tbl1

Upvotes: 4

Zohar Peled
Zohar Peled

Reputation: 82474

If the @@ROWCOUNT is 0, the stored procedure will return two result sets - the first one will be empty and the second one might have rows.

You need re-write the query to produce a single result set no matter if it was selected from table2 or table2_archive.

One way to do it, assuming no duplicated rows in table2 and table2_archive is to use a common table expression and union all like this:

WITH CTE AS
(
    SELECT  data, id
    FROM    table2 
    UNION ALL 
    SELECT  data, id
    FROM    table2_archive 
)

SELECT  a.code, a.name
FROM    cte b
        INNER JOIN table1 a ON a.id = b.id
WHERE   b.data = @parameter
        AND b.condition = 1

If there are duplicated rows, a slightly more cumbersome solution can be applied:

WITH CTE AS
(
    SELECT  a.code, a.name
    FROM    table2 b
            INNER JOIN table1 a ON a.id = b.id
    WHERE   b.data = @parameter
            AND b.condition = 1
)

SELECT code, name
FROM CTE
UNION ALL

SELECT  a.code, a.name
FROM    table2_archive b
        INNER JOIN table1 a ON a.id = b.id
WHERE   b.data = @parameter
        AND b.condition = 1
        AND NOT EXISTS (
            SELECT 1 
            FROM CTE
        )

Upvotes: 0

Related Questions