Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Insert Update Stored Procedure - HELP Expand / Collapse
Author
Message
Posted Thursday, September 30, 2010 9:38 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Monday, July 15, 2013 1:51 PM
Points: 12, Visits: 23
I 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 company_id which is an identity column. 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 will not do the Insert!! 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

ALTER PROCEDURE [dbo].[fm_Company_InsertUpdate]

@INCMP int = -1,
@CNAME VARCHAR(40),
@LEGAL VARCHAR(40),
@TAXID VARCHAR(40) = null,
@CMP int OUTPUT

AS

BEGIN

-- 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();
END
END

Post #996392
Posted Thursday, September 30, 2010 10:00 PM


SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: Yesterday @ 2:33 PM
Points: 4,009, Visits: 6,072
Add 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:

http://technet.microsoft.com/en-us/library/ms187316.aspx


__________________________________________________

Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? -- Stephen Stills
Post #996399
Posted Friday, October 1, 2010 8:43 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Monday, July 15, 2013 1:51 PM
Points: 12, Visits: 23
Thanks 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!
Post #996716
Posted Friday, October 1, 2010 8:54 AM


SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: Yesterday @ 2:33 PM
Points: 4,009, Visits: 6,072
You got it !! A+

A 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.

 -- 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




__________________________________________________

Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? -- Stephen Stills
Post #996734
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse