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