2

I always create seperate insert and update procedures e.g insert employee and update employee however im getting bored of having to update both procedures when a new field is added. Any thoughts on this. How do people handle inserts and updates in sql / stored procedures

Improve this question
2
  • Usually, this is a good indicator that a solutions architecture is lacking proper design up front. If you're having to go back and constantly change your data schema as well as supporting procedures on a regular basis, you're simply wasting time developing against an idea/concept that wasn't solidified to start with. I doubt you'll find a silver bullet to this kind of issue -- this is just one of those things you have to suck up and deal with. Commented Aug 8, 2011 at 15:37
  • Is this SQL Server 2008 or earlier? Commented Aug 8, 2011 at 15:43

5 Answers 5

Reset to default
3

For SQL Server 2008+, there is the MERGE command to consider.

Improve this answer
Sign up to request clarification or add additional context in comments.

Comments

1

One trick is add an ID field to the parameters. If ID is -1, insert new record. If something else, update.

Improve this answer

Comments

1

T-SQL Insert or update

is a good example of what you want. Actually, the question itself shows a solution :)

Improve this answer

4 Comments

I favor Option 2 over 1. Why execute UPDATE and check ROWCOUNT when you can just check if the record exists first.
Why not just try and INSERT, UPDATE if error? stackoverflow.com/questions/5504167/…
@adamcodes, Oddly, no, It touches the table less times then testing first: an INSERT will check uniqueness anyway. And it's highly concurrent. See lesson 4: sqlblog.com/blogs/paul_nielsen/archive/2007/12/12/…
@w00te i was under the impression that if statements in stored procs were bad practice as the server would only optimise the stored proc the first time it ran.
0

You might be able to externally treat everything as an update and then substitute an insert if the record does not exists but not sure that would be any easier or cleaner. Another option may be to create a generalized stored procedure for any text then pass the field name to the procedure but that exposes your stored procedure to hacks and kind of defeats the security benefits of a stored procedure. What I have is a generalized class for textField and a generalized table for text where the textField table has fieldID as part of the key so it houses multiple text fields. From the textField class I call SQL directly but I could re-factor it to call a stored procedure. Then I have another generalized table and class for date fields ...

Improve this answer

1 Comment

The answer by wOOte is what a great example of treat everything as an update then substitute...
0

This can be done inside one stored procedure by simply using Labels.

For example:

 CREATE PROCEDURE dbo.spAddUpdateXXXXXXX @UniqueId bigint, @Value01 int, @Value02 varchar(50), /* Other Inputs Go Here */ @ValueEnd datetime AS Set NoCount On IF @UniqueId < 1 GOTO InsertRecord -------------------------------------------------------------------- UpdateRecord: -------------------------------------------------------------------- UPDATE myTable SET Bla Bla Bla GOTO EndProcessing -------------------------------------------------------------------- InsertRecord: -------------------------------------------------------------------- INSERT INTO myTable Bla Bla Bla GOTO EndProcessing -------------------------------------------------------------------- EndProcessing: -------------------------------------------------------------------- RETURN 0 Set NoCount Off
Improve this answer

Comments

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.