Combining Insert/Update to one Procedure






4.09/5 (11 votes)
Why do developers insist on separate procedures to do these jobs
I can never understand why developers have both an
Insert
and an Update
procedure, they do basically the same thing. If you combine them, you reduce the CRUD procedures by 25%, your DAL has 1 less method and management is simpler.
By simply managing the ID field(s), you can combine these operations into 1 procedure.
ALTER PROC [APExclusionUpdate] --Declare @ExclID INT, @EntityID INT, @AttrTypeID INT, @APLinkID INT, @Modified DATETIME, @ModifiedBy VARCHAR(100) AS -------------------- Created by Classbuilder ----------- IF ISNULL(@ExclID, 0) = 0 -- decide to Insert based on ID BEGIN INSERT INTO APExclusion (EntityID, AttrTypeID, APLinkID, Modified, ModifiedBy ) VALUES (@EntityID, @AttrTypeID, @APLinkID, @Modified, @ModifiedBy ) SELECT SCOPE_IDENTITY() AS ExclID -- return the new ID value END ELSE BEGIN UPDATE APExclusion SET EntityID = @EntityID, AttrTypeID = @AttrTypeID, APLinkID = @APLinkID, Modified = @Modified, ModifiedBy = @ModifiedBy WHERE ExclID = @ExclID SELECT @ExclID AS ExclID -- return the passed in ID value ENDThis procedure decides whether to insert or update the record based on the ID fields. You can either return the ID or the new/updated record. I generally return the ID. I have used this with both identity and GUID primary keys, GUID requires a bit more management.