<FONT size=2><SPAN class=843211217-19072005>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.

<FONT size=2><SPAN class=843211217-19072005><SPAN class=cb1><FONT color=#0000ff> 

<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"> <P style="MARGIN: 0px">    1 CREATE FUNCTION fnGetEmployeesByCity3 (@sCity VARCHAR(30))</P> <P style="MARGIN: 0px">    2     RETURNS @tblMyEmployees TABLE</P> <P style="MARGIN: 0px">    3     (</P> <P style="MARGIN: 0px">    4         FirstName VARCHAR(20),</P> <P style="MARGIN: 0px">    5         LastName VARCHAR(40),</P> <P style="MARGIN: 0px">    6         Address VARCHAR(120)</P> <P style="MARGIN: 0px">    7     )</P> <P style="MARGIN: 0px">    8 AS</P> <P style="MARGIN: 0px">    9 BEGIN</P> <P style="MARGIN: 0px">   10     INSERT   @tblMyEmployees</P> <P style="MARGIN: 0px">   11     SELECT   FirstName, LastName, Address</P> <P style="MARGIN: 0px">   12     FROM     Employees</P> <P style="MARGIN: 0px">   13     WHERE    City = @sCity </P> <P style="MARGIN: 0px">   14     ORDER BY LastName</P> <P style="MARGIN: 0px">   15 </P> <P style="MARGIN: 0px">   16     IF NOT EXISTS (SELECT * FROM @tblMyEmployees)</P> <P style="MARGIN: 0px">   17         INSERT @tblMyEmployees (Address)</P> <P style="MARGIN: 0px">   18             VALUES (‘No matching employees found in the specified city’)</P> <P style="MARGIN: 0px">   19 </P> <P style="MARGIN: 0px">   20     RETURN</P> <P style="MARGIN: 0px">   21 END</P></DIV></FONT></SPAN></DIV>

John Papa

Evangelist on the Loose