InstantForum.NET v2.9.0SQLServerCentralhttp://www.sqlservercentral.com/Forums/notifications@sqlservercentral.comFri, 24 May 2013 07:44:38 GMT20http://www.sqlservercentral.com/Forums/Topic996392-392-1.aspxYou got it !! A+ :-DA good practice is to define a variable (I always use @RC.) and set it to the value of @@ROWCOUNT after any event that you want to test for results. This allows for intermediate statements without losing the value originally trapped by @@ROWCOUNT.[code] -- Update the row if it exists. Update dbo.fm_Company SET company_name=@cname, company_legal_name=@legal, company_tax_id=@taxid Where company_id=@INCMP; SET @RC = @@ROWCOUNT SET @CMP = @INCMP; -- Insert the row if the UPDATE statement failed. IF (@RC = 0) -- @RC didn't get reset when @@ROWCOUNT did BEGIN [/code]Fri, 01 Oct 2010 08:54:57 GMTThe Dixie Flatlinehttp://www.sqlservercentral.com/Forums/Topic996392-392-1.aspxThanks for the help! I see the error of my ways!! I made things difficult for myself by copying an SP I wrote at work. That SP does not do a set after the update statement. I get it now, the @@ROWCOUNT is getting set to 1 by my Set @CMP line!Lesson learned!!Thanks!Fri, 01 Oct 2010 08:43:55 GMTDaGmenhttp://www.sqlservercentral.com/Forums/Topic996392-392-1.aspxAdd a PRINT @@rowcount immediately after the SET statement. Bet a nickel that it is greater than 0. If so, think about *why* and let us know if you can't figure it out from there. Here's one more hint. Your answer is somewhere on this page:[url=http://technet.microsoft.com/en-us/library/ms187316.aspx]http://technet.microsoft.com/en-us/library/ms187316.aspx[/url]Thu, 30 Sep 2010 22:00:01 GMTThe Dixie Flatlinehttp://www.sqlservercentral.com/Forums/Topic996392-392-1.aspxI have created a stored procedure that does an update and insert to a small table. The SP is very simple. The table that is being used has a column called [b]company_id[/b] which is an [b]identity column[/b]. On inserts this value is returned to the calling code. The table is currently empty and I am testing the SP. Thus right now it should do an insert and return the ID to the calling code. I have run this SP in SQL mgmt studio and in debug from Visual Studio 2008 and it just [b]will not do the Insert!![/b] It is driving me crazy. I am sure it is something very simple and/or obvious that I am missing. My SP code is listed below[code="sql"]ALTER PROCEDURE [dbo].[fm_Company_InsertUpdate]@INCMP int = -1,@CNAME VARCHAR(40),@LEGAL VARCHAR(40),@TAXID VARCHAR(40) = null,@CMP int OUTPUTASBEGIN -- Update the row if it exists. Update dbo.fm_Company SET company_name=@cname, company_legal_name=@legal, company_tax_id=@taxid Where company_id=@INCMP; SET @CMP = @INCMP; -- Insert the row if the UPDATE statement failed. IF (@@ROWCOUNT = 0) BEGIN --Insert record INSERT INTO dbo.fm_Company ([company_name],[company_legal_name],[company_tax_id]) VALUES (@CNAME, @LEGAL, @TAXID) SET @CMP = SCOPE_IDENTITY(); ENDEND[/code]Thu, 30 Sep 2010 21:38:40 GMTDaGmen