How to use @@ ROWCOUNT in an IF statement, as well as in a 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 saying correctly that this will always return 1 for @@ ROWCOUNT, since it is the last one to complete the task?

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

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

Answer 1

The reason it returns one is because you assigned exactly one value (some kind of arbitrary value). You should use EXISTS instead of doing the score manually, as @Sive suggests. The reason is that EXISTS can perform just as well as COUNT, and in the case you are describing, it doesn’t really matter what the actual score is. You want to know if the count is zero or greater than zero.

The problem with assigning this value to a variable as you did it, what happens if there are several lines that match? Let's try:

 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 cost. This is unacceptable when a subquery follows = ,! =, <, <=,>,> = or when the subquery is used as an expression.

Now, if varname unique, I would do the following:

 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 not unique, then I'm not sure what you are going to do with a single value. Which one did it? 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 if you only capture one of the potentially many values? Are you going to do something with this line, but not with others?

In addition, if you intend to insert data from this table, why not just throw out the preliminary check, the number of rows, etc. and just say:

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

Insertion will not occur if the row does not exist.

Answer 2

You can do something similar using EXISTS . This will check if at least one entry exists for the given value in @varName before executing your statements between BEGIN and END.

Script:

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

MSDN Documentation: EXISTS (Transact-SQL)

Based on the comments, update # 1:

I do not understand the requirement to know the meaning. I think you can use COUNT if you want to get an account.

 DECLARE @recordsCount SELECT @recordsCount = COUNT(var) FROM dbo.varDB WHERE varName = @varName IF recordsCount > 0 BEGIN // your statements END 

Based on the comments, update # 2:

If you want to use the result set inside BEGIN and END, then the question needs more detailed information on how the result set will be used inside statements. Based on additional data, you may not even need an IF expression. Additional information needed to provide a clear solution to your problem.

Answer 3

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

If your query does not return strings, @var is still NULL .
If it returns a single line, @var will have a value
If it returns more than one row, you will get 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 , you can instead 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 4

You can select and check the number of rows returned:

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