How to use @@ROWCOUNT in IF statement as well as within BEGIN..END block?

Question

My code is as follows:

SET @var = (SELECT var FROM dbo.varDB WHERE varName = @varName)

IF (@@ROWCOUNT > 0)
    BEGIN
        //carry out insert
    END

Am I right in saying this will always return a 1 for @@ROWCOUNT as it carries out an assignment last?

If so, what would be the most elegant solution to allow me to:

• Create variable with the result from my query stored in it
• Test if the query returns any results

Answer 1

You can set your variable to NULL (just to be sure).

If your query returns no rows, @var is still NULL.
If it returns one row, @var will have a value
If it returns more than one row you will have an exception.

SET @var = NULL

SET @var = (SELECT var FROM dbo.varDB WHERE varName = @varName)
IF @var IS NOT NULL
BEGIN
//carry out insert
END

Update

If you really want to use @@ROWCOUNT instead you can assign your value using select instead of set.

SELECT @var = var FROM dbo.varDB WHERE varName = @varName
IF (@@ROWCOUNT > 0)
BEGIN
//carry out insert
END

Answer 2

The reason it returns one is because you've assigned exactly one value (some arbitrary value). You should be using EXISTS instead of doing a manual count anyway, as @Sive suggests. The reason is that EXISTS can perform no worse than COUNT, and in the use case you're describing, it doesn't really matter what the actual count is anyway. You want to know if the count is zero or greater than zero.

The problem with assigning that value to a variable in the way you've done it, is what happens if there are multiple rows that match? Let's try it:

DECLARE @foo TABLE([var] INT, varname SYSNAME);
INSERT @foo VALUES (1,N'bob'),(2,N'bob');
DECLARE @var INT;
SET @var = (SELECT [var] FROM @foo WHERE varname = N'bob

Result:

Msg 512, Level 16, State 1, Line 4
Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.

Now, if varname is unique, I would do this:

SELECT @var = [var] FROM dbo.varDB WHERE varName = @varName;
IF @var IS NOT NULL
BEGIN
    // carry out insert
END
ELSE
BEGIN
    PRINT 'Already existed! ' + RTRIM(@var);
END

If varname is not unique, then I'm not sure what you're going to do with a single value. Which one did it pull? Imagine this scenario:

DECLARE @foo TABLE([var] INT, varname SYSNAME);
INSERT @foo VALUES (3,N'bob'),(2,N'adam'),(1,N'bob');
DECLARE @var INT;
SELECT @var = [var] FROM @foo WHERE varname = N'bob';
PRINT @var;

Will @var be 1 or 3? Who knows? Why does it matter which one it is if you're only capturing one of potentially many values? Are you going to do something with that row but not the others?

In addition, if your intention is to insert data from this table, why not simply throw out the up-front check, the row count etc. and just say:

INSERT dbo.SomeTable(column1 --,...other columns
  SELECT var --,...other columns
  FROM dbo.varDB
  WHERE varName = @varName;

The insert won't happen if the row doesn't exist.