January 19, 2006 at 7:44 am
Looking for opinions on a stored proc that checks to see if an existing records exists and updates if it does else inserts a new record.
Example:
Create PROCEDURE updateinfo
@userid int,
@name varchar(50)
AS
if userid exists (select userid from main where userid=@userid) begin
update statement goes here
end
else begin
insert statement goes here
end
January 19, 2006 at 7:51 am
I think you want
if EXISTS (select userid from main where userid=@userid) begin -- remove 'userid'
update statement goes here
end
else begin
insert statement goes here
end
January 20, 2006 at 4:19 am
Alternatively:
update statement goes here
if @@ROWCOUNT = 0 -- i.e if no rows were updated
insert statement goes here
January 20, 2006 at 5:35 am
Yup, Jesper's way is the most efficient if the update has a higher probability of occurring than the insert. Alternately, if you try the insert and use any dupe-key error to invoke an update, the error mechanics takes more processing (assumption) than the first update/insert flow. We use the Jesper logic in many places.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply