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.
Started my programming life writing Excel 1.0 macros, God what a long time ago.
Now I'm a dotnet developer, I get to influence direction, play with new toys, build stuff, life is wonderful.
Greatest buzz you can get, walk past a row of desks and see your application running on all of them (and getting paid).
Greatest irritant, pouring 12 months of knowledge and experience into an empty head only to have it leave.
And now I'm retired, no deadlines, no meetings, no managers (except the ONE) and no users!