one stored procedure to insert/update

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

one stored procedure to insert/update

Author

Topic

bkhendershot
Starting Member

1 Post

Posted - 2010-03-17 : 15:25:41

Hi,

i have a question about using a single stored procedure to do both
update and insert data.

a lot of suggestions i have seen online do something along the lines of
- update first, then if rowcount is 0, insert
or
- use if exists

These methods do not seem ideal due to concurrency issues and multiple hits to the database.

what i wanted to do with mine is have the primary key passed in to the sproc @ID. Then, have the calling program just pass a 0 if it's a new record, or the ID if it isn't.
then, i can check the parameter to know how to act instead of the database ...

If @ID = 0
BEGIN
INSERT INTO ....
END
ELSE
BEGIN
UPDATE xxx
WHERE ID = @ID
END

Am I missing something here?
Do the other methods have some benefit I'm not considering?

Would like some feedback.

Thanks!

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2010-03-17 : 16:02:07

If you have the chance to do it like this then I think it is fine.

No, you're never too old to Yak'n'Roll if you're too young to die.

Kristen
Test

22859 Posts

Posted - 2010-03-17 : 16:03:34

That's fine if you have IDENTITY for the ID; but if for example you are adding 3rd party Product Codes, and using their SKU as the PK, then two people could add the same one at the same time.

All our CRUD SProcs do "UpSert".

We have a parameter to indicate "This is an insert" (fails if any Unique Index [including PK] already exists) / "This is an update" (fails if record does not already exist)

We have a column in all our tables for "Edit Count" (same sort of thing as ROWVERSION) and an update will fail if the ROWVERSION is not as expect (i.e. someone else edited the record in the interim). We also have parameter for "I don't care what the ROWVERSION is, just UPDATE it - but it must already exist" and so on.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources