CODEWITHSUNDEEP
c#sql-server
Chandra sekhar
Chandra sekhar

Reputation: 167

Regarding insert and update stored procedure

I have a registration page and at first I want to insert some mandatory fields into the DB, after that in the second page I want to update some more details according to the userid.

So I want to do this with stored procedure which is the best to way to do it,creating an insert and update stored procedure separately or creating a single procedure which have insert and update?

Which is best in terms of performance?

Upvotes: 0

Views: 243

Answers (5)

Aghilas Yakoub
Aghilas Yakoub

Reputation: 29000

You can create a procedure for insert and another for update

Upvotes: 0

Jon Hanna
Jon Hanna

Reputation: 113382

Any performance difference will be pretty negligible. There's bound to be a difference of a couple of microseconds in there somewhere, but it really will just be that small, and a bunch of other little decisions are going to wipe out the difference.

The only real difference then, is how clear the code would be. It seems to me from your description that "create a new user" and "update a user" are being treated as separate concerns by the rest of your code, and so should be separate.

If there was some reason why your code would find it easier to deal with a "create or update as appropriate" operation, then that would be a different matter, but there's nothing to suggest it's the case here.

Upvotes: 0

bobwah
bobwah

Reputation: 2568

writing a separate insert and update procedure would be more efficient as the procedure does not have to consider whether to insert or update though the efficiency saving is going to be minimal so I wouldn't make a decision based on this and it should be more based around readability and understandability.

Upvotes: 0

XN16
XN16

Reputation: 5889

You could create two separate stored procedures, one for INSERT and one for UPDATE. That would be simple enough for anyone to understand.

However if you are using MSSQL 2008 or newer then you could make a single stored procedure and utilise MERGE:

Technet SQL MERGE

Upvotes: 0

Paul Aldred-Bann
Paul Aldred-Bann

Reputation: 6020

There's no performance gain from having these in the same query, as two separate stored procedures are still pre-compiled by the database server. This is more of a maintenance issue than performance - I'd suggest having separate stored procedures for insert and update which is far more readable and obvious than mashing them together in a single script.

Upvotes: 1

Related Questions