Insert Update Stored Procedure - HELP

Question

Post reply

Insert Update Stored Procedure - HELP

DaGmen

SSC-Addicted

Points: 432
More actions
September 30, 2010 at 9:38 pm

#224576

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

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

The Dixie Flatline SSC Guru Points: 53354 More actions · Answer 1

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? Everybody look what's going down. -- Stephen Stills

DaGmen SSC-Addicted Points: 432 More actions · Answer 2

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!

The Dixie Flatline SSC Guru Points: 53354 More actions · Answer 3

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? Everybody look what's going down. -- Stephen Stills