is "select count(*) from..." more reliable than @@ROWCOUNT?

I have a proc that inserts records to a temp table. In pseudocode it looks like this:

Create temp table 
a. Insert rows into temp table based on stringent criteria
b. if no rows were inserted, insert based on less stringent criteria 
c. if there are still no rows, try again with even less stringent criteria
select from temp table

There are a lot of IF @@rowcount = 0 checks in the code to see if the table has any rows. The issue is that the proc isn't really doing what it looks like it should be doing and it's inserting the same row twice (开发者_运维百科steps a and c are being executed). However, if I change it to check this way IF ( (select count(*) from @temp) = 0) the proc works exactly as expected.

Which makes me think that @@rowcount isn't the best solution to this problem. But I'm adding in extra work via the SELECT COUNT(*). Which is the better solution?

@@rowcount is the better solution. The work is already done. Selecting count(*) causes the database to do more work.

You need to make sure you are not doing something that will affect the value of @@rowcount before checking the value of @@rowcount. It is usually best to check @@rowcount immediately after performing the insert statement. If necessary assign the value to a variable so you can check it later:

DECLARE @rows int 
...
[insert or update a table]
SET @rows = @@rowcount

Storing the row count immediately after any operation that changes row count will allow you to use the value more than once.

@@ROWCOUNT will just check "affected rows" from the previous query. This can be a little far reaching as many things can create rows or "affect" rows and some options don't return a value to the client.

Microsoft says:

Statements that make an assignment in a query or use RETURN in a query set the @@ROWCOUNT value to the number of rows affected or read by the query, for example: SELECT @local_variable = c1 FROM t1.

Data manipulation language (DML) statements set the @@ROWCOUNT value to the number of rows affected by the query and return that value to the client. The DML statements may not send any rows to the client.

DECLARE CURSOR and FETCH set the @@ROWCOUNT value to 1.

EXECUTE statements preserve the previous @@ROWCOUNT.

Statements such as USE, SET , DEALLOCATE CURSOR, CLOSE CURSOR, BEGIN TRANSACTION or COMMIT TRANSACTION reset the ROWCOUNT value to 0.

If you are not getting what you expect from @@ROWCOUNT (which probably means your query is more complex than your example) I would definitely be looking at using the SELECT COUNT(*) option, or, if you're worried about the performance hit do something like this:

INSERT INTO temptable
(cols...)
SELECT
 COL = @VAL
FROM
 sourcetableorquery
 LEFT JOIN temptable on [check for existing row]
WHERE
 temptable.id is null

This will be faster than the count option if you are looping over a big recordset.

If it's ever being used twice in a row, that could be a problem:

http://beyondrelational.com/blogs/madhivanan/archive/2010/08/09/proper-usage-of-rowcount.aspx