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

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
Richard Banks
  • 2,946
  • 5
  • 34
  • 71
  • 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. – George Johnston Aug 08 '11 at 15:37
  • Is this SQL Server 2008 or earlier? – gbn Aug 08 '11 at 15:43

5 Answers5

3

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

Joe Stefanelli
  • 132,803
  • 19
  • 237
  • 235
1

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

adamcodes
  • 1,606
  • 13
  • 21
1

T-SQL Insert or update

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

Community
  • 1
  • 1
John Humphreys
  • 37,047
  • 37
  • 155
  • 255
  • I favor Option 2 over 1. Why execute UPDATE and check ROWCOUNT when you can just check if the record exists first. – adamcodes Aug 08 '11 at 15:39
  • Why not just try and INSERT, UPDATE if error? http://stackoverflow.com/questions/5504167/is-it-ok-to-check-rowcount-function-to-determine-existence-of-a-row/5504478#5504478 – gbn Aug 08 '11 at 15:44
  • 1
    @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: http://sqlblog.com/blogs/paul_nielsen/archive/2007/12/12/10-lessons-from-35k-tps.aspx – gbn Aug 08 '11 at 16:38
  • @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. – Richard Banks Aug 10 '11 at 08:10
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 ...

paparazzo
  • 44,497
  • 23
  • 105
  • 176
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 &lt 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
Michael Riley - AKA Gunny
  • 5,074
  • 4
  • 42
  • 89