I am new to the code review area of Stack and welcome all positive criticism.
I would be grateful if someone could review my code below and let me know where I can improve or have gone wrong that I cannot see.
The only thing that I have left is to write the delete and would appreciate help in doing this is possible too please.
My code is
CREATE TABLE CRM_PERS_CONTACT_INFORMAITON(
SEQ_ID INT IDENTITY(1,1) not null primary key,
PERS_ID INT NOT NULL,
PREFERRED BIT DEFAULT (0),
CEASED_DATE DATETIME,
COMMUNICATION_TYPE VARCHAR(50),
COMMUNICATION_VALUE VARCHAR(255),
DATE_ADDED DATETIME DEFAULT (GETDATE()),
ADDED_BY VARCHAR(50),
UPDATED_DATE DATETIME DEFAULT(GETDATE()),
UPDATED_BY VARCHAR(50)
)
GO
CREATE TABLE CRM_PERS_CONTACT_INFORMAITON_HISTORY(
SEQ_ID INT IDENTITY(1,1) not null primary key,
PERS_ID INT NOT NULL,
PREFERRED BIT DEFAULT (0),
CEASED_DATE DATETIME,
COMMUNICATION_TYPE VARCHAR(50),
COMMUNICATION_VALUE VARCHAR(255),
DATE_ADDED DATETIME DEFAULT (GETDATE()),
ADDED_BY VARCHAR(50),
ACTION_TYPE VARCHAR(20)
)
GO
CREATE PROCEDURE MERGE_CRM_PERS_CONTACT_INFORMATION
@SEQ_ID INT,
@PERS_ID INT ,
@PREFERRED BIT ,
@CEASED_DATE DATETIME,
@DATE_ADDED DATETIME,
@ADDED_BY VARCHAR,
@UPDATED_DATE DATETIME,
@UPDATED_BY VARCHAR,
@COMMUNICATION_TYPE VARCHAR,
@COMMUNICATION_TYPE_VALUE VARCHAR,
@ACTION_TYPE VARCHAR
AS
BEGIN
MERGE CRM_PERS_CONTACT_INFORMAITON as Target
USING (select
@PERS_ID
,@PREFERRED
,@CEASED_DATE
,@DATE_ADDED
,@ADDED_BY
,@UPDATED_DATE
,@UPDATED_BY
,@COMMUNICATION_TYPE
,@COMMUNICATION_TYPE_VALUE
) AS Source(
PERS_ID
,PREFERRED
,CEASED_DATE
,DATE_ADDED
,ADDED_BY
,UPDATED_DATE
,UPDATED_BY
,COMMUNICATION_TYPE
,COMMUNICATION_TYPE_VALUE
)
ON (Source.PERS_ID = Target.PERS_ID
AND Source.COMMUNICATION_TYPE = Target.COMMUNICATION_TYPE)
WHEN MATCHED THEN
update SET
COMMUNICATION_TYPE = @COMMUNICATION_TYPE
,COMMUNICATION_VALUE = @COMMUNICATION_TYPE_VALUE
,UPDATED_DATE = GETDATE()
,UPDATED_BY = @UPDATED_BY
,CEASED_DATE = @CEASED_DATE
,PREFERRED = @PREFERRED
WHEN NOT MATCHED BY TARGET THEN
INSERT (
PERS_ID
,PREFERRED
,CEASED_DATE
,COMMUNICATION_TYPE
,COMMUNICATION_VALUE
,DATE_ADDED
,ADDED_BY
,UPDATED_DATE
,UPDATED_BY
)
VALUES (
@PERS_ID
,@PREFERRED
,@CEASED_DATE
,@COMMUNICATION_TYPE
,@COMMUNICATION_TYPE_VALUE
,@DATE_ADDED
,@ADDED_BY
,GETDATE()
,@UPDATED_BY
)
WHEN NOT MATCHED BY SOURCE THEN
DELETE;
SET @ACTION_TYPE = $action
INSERT INTO CRM_PERS_CONTACT_INFORMAITON_HISTORY(
PERS_ID
,PREFERRED
,CEASED_DATE
,COMMUNICATION_TYPE
,COMMUNICATION_VALUE
,DATE_ADDED
,ADDED_BY
,ACTION_TYPE
)
VALUES (
@PERS_ID
,@PREFERRED
,@CEASED_DATE
,@COMMUNICATION_TYPE
,@COMMUNICATION_TYPE_VALUE
,GETDATE()
,@ADDED_BY
,@ACTION_TYPE
);
END
GO