The previous store procedure have records repeated and here is the improve one.
the table like this:
CREATE PROCEDURE [dbo].[spStoreLMStatusRecording]
@parameterJobID varchar(30),
@parameterLMStatus nvarchar(10),
@parameterEntry_datetime datetime
AS
BEGIN
declare
@username varchar(500),
@Job_Acceptance bit
SET @Job_Acceptance = '1'
IF NOT EXISTS(SELECT * FROM tblJob where JobID = @parameterJobID)
begin
insert into tblJob values(@parameterJobID,@Job_Acceptance);
select @username = username from tblUserAuthentication
insert into tblLMStatus values(@parameterLMStatus,@parameterEntry_datetime,@parameterJobID,@username)
end
else
begin
select @username = username from tblUserAuthentication
insert into tblLMStatus values(@parameterLMStatus,@parameterEntry_datetime,@parameterJobID,@username)
end
END