T-SQL: IF NOT EXISTS versus @@ROWCOUNT

I was asked a good question the other day regarding why I chose to use "IF NOT EXISTS" in my Nov 2003 MSDN article on User Defined Functions to check to see if a declared table variable had any rows in it versus checking @@ROWCOUNT. Mostly I chose to use IF NOT EXISTS because I know that I find it less apt to break on me in case I do something silly. For example, if I chose to check @@ROWCOUNT in the SQL sample code below instead of using "IF NOT EXISTS", it works just fine. But what happens if I (or someone else) adds another SQL statement at line 15 that alters the @@ROWCOUNT? For example, If I augment this code in the future to inlcude an UPDATE statement at line 15, the ROWCOUNT will no longer represent the number of rows affected by the INSERT statement (lines 10-14). Is this likely to happen? Probably not. Has it happened to me? Oh yeah, I was burnt once on this. Is it bad to use ROWCOUNT? Not at all! I was being overly careful in this code example and also wanted to show how "IF NOT EXISTS" works. So the answer is that both ways work.

<DIV class=cf> <DIV style="BORDER-RIGHT: windowtext 1pt solid; PADDING-RIGHT: 0pt; BORDER-TOP: windowtext 1pt solid; PADDING-LEFT: 0pt; FONT-SIZE: 8pt; BACKGROUND: white; PADDING-BOTTOM: 0pt; BORDER-LEFT: windowtext 1pt solid; COLOR: black; PADDING-TOP: 0pt; BORDER-BOTTOM: windowtext 1pt solid; FONT-FAMILY: Courier New"> 1 CREATE FUNCTION fnGetEmployeesByCity3 (@sCity VARCHAR(30)) 2 RETURNS @tblMyEmployees TABLE 3 ( 4 FirstName VARCHAR(20), 5 LastName VARCHAR(40), 6 Address VARCHAR(120) 7 ) 8 AS 9 BEGIN 10 INSERT @tblMyEmployees 11 SELECT FirstName, LastName, Address 12 FROM Employees 13 WHERE City = @sCity 14 ORDER BY LastName 15 16 IF NOT EXISTS (SELECT * FROM @tblMyEmployees) 17 INSERT @tblMyEmployees (Address) 18 VALUES (‘No matching employees found in the specified city’) 19 20 RETURN 21 END</DIV></DIV>

T-SQL: IF NOT EXISTS versus @@ROWCOUNT

John Papa

Latest Projects