SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Insert Update Stored Procedure - HELP


Insert Update Stored Procedure - HELP

Author
Message
DaGmen
DaGmen
Grasshopper
Grasshopper (22 reputation)Grasshopper (22 reputation)Grasshopper (22 reputation)Grasshopper (22 reputation)Grasshopper (22 reputation)Grasshopper (22 reputation)Grasshopper (22 reputation)Grasshopper (22 reputation)

Group: General Forum Members
Points: 22 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


The Dixie Flatline
The Dixie Flatline
SSCertifiable
SSCertifiable (5.3K reputation)SSCertifiable (5.3K reputation)SSCertifiable (5.3K reputation)SSCertifiable (5.3K reputation)SSCertifiable (5.3K reputation)SSCertifiable (5.3K reputation)SSCertifiable (5.3K reputation)SSCertifiable (5.3K reputation)

Group: General Forum Members
Points: 5348 Visits: 6900
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
DaGmen
Grasshopper
Grasshopper (22 reputation)Grasshopper (22 reputation)Grasshopper (22 reputation)Grasshopper (22 reputation)Grasshopper (22 reputation)Grasshopper (22 reputation)Grasshopper (22 reputation)Grasshopper (22 reputation)

Group: General Forum Members
Points: 22 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!
The Dixie Flatline
The Dixie Flatline
SSCertifiable
SSCertifiable (5.3K reputation)SSCertifiable (5.3K reputation)SSCertifiable (5.3K reputation)SSCertifiable (5.3K reputation)SSCertifiable (5.3K reputation)SSCertifiable (5.3K reputation)SSCertifiable (5.3K reputation)SSCertifiable (5.3K reputation)

Group: General Forum Members
Points: 5348 Visits: 6900
You got it !! A+ :-D

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
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search