SP to insert IF new UPDATE IF existing

ASP Free Forums Sponsor:

July 27th, 2004, 11:52 AM

rocknc

Registered User

Join Date: Jul 2004

Location: USA

Posts: 5

Time spent in forums: < 1 sec
Reputation Power: 0

SP to insert IF new UPDATE IF existing

I have a SP that inserts a record:

Code:

IF @action = 'add'
 BEGIN
INSERT INTO OrderDetail
(orderid, itemid, iquantity)
VALUES
(@orderid, @itemid, @itemQ)
 END

I want to add a nested IF statement to check to see if a record exist WHERE orderid AND itemid = @orderid AND @itemid.

I am thinking I should use something like:

Code:

 
SET @orderlineid int = SELECT orderlineid FROM OrderDetail WHERE orderid = @orderid AND itemid = @itemid
 
IF (@orderlineid NOT NULL)
BEGIN
UPDATE OrderDetail SET iquantity = @itemQ WHERE orderlineid = @orderlineid
ELSE
INSERT INTO OrderDetail
(orderid, itemid, iquantity)
VALUES
(@orderid, @itemid, @itemQ)
END

Is there a better way?

thanks

July 27th, 2004, 01:45 PM

rocknc

Registered User

Join Date: Jul 2004

Location: USA

Posts: 5

Time spent in forums: < 1 sec
Reputation Power: 0

What worked for me

This is what I came up with in case anyone is interested:

Code:

  
IF @action = 'add'
 BEGIN
/* Check for existing item in order */ 
IF EXISTS (SELECT orderlineid FROM OrderDetail WHERE orderid = @orderid AND itemid = @itemid)
 BEGIN
DECLARE @orderlineid int
DECLARE @orderQ int
SET @orderlineid = (SELECT orderlineid FROM OrderDetail WHERE orderid = @orderid AND itemid = @itemid)
SET @orderQ = (SELECT iquantity FROM OrderDetail WHERE orderid = @orderid AND itemid = @itemid)
SET @orderQ = @orderQ + @itemQ
UPDATE OrderDetail SET iquantity = @orderQ WHERE orderlineid = @orderlineid
 END
ELSE
 BEGIN
INSERT INTO OrderDetail
(orderid, itemid, iquantity)
VALUES
(@orderid, @itemid, @itemQ)
 END
 END

Viewing: ASP Free Forums > Database > Microsoft SQL Server > SP to insert IF new UPDATE IF existing

« Previous Thread | Next Thread »

Thread Tools	Search this Thread
Email this Page	Search this Thread: Advanced Search
Display Modes	Rate This Thread
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode	Rate This Thread:

Posting Rules
You may not post new threads You may not post replies You may not post attachments You may not edit your posts vB code is On Smilies are On [IMG] code is On HTML code is Off

View Your Warnings | New Posts | Latest News | Latest Threads | Shoutbox

Forum Jump