Reputation: 2983

tsql inserts and updates

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

Upvotes: 2

Answers (5)

Michael Riley - AKA Gunny

Reputation: 5184

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

Upvotes: 0

paparazzo

Reputation: 45096

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 ...

Upvotes: 0