Updating A Column With Scope_Identity After An Insert Sometimes Does Not Work

Hello,

I have the following stored procedure that inserts records and updates the new record. The parameter @rpt_id has a value of -1 when entering the procedure. It needs to be updated with the new record if (identity) once the record is inserted, bu sometimes the update does not happen. The new records ends up with -1 in the rpt_id column.

I have included the stored procedure. I will appreciate any ideas?


ALTER PROCEDURE [dbo].[thhill_InsertReport]

(

@RPT_ID int,

@REPORT_DATE datetime,

@COMMIT_DATE datetime,

@JOB_NUMBER nvarchar(50),

@TECH_NAME nvarchar(75),

@CLIENT_NAME nvarchar(255),

@CLIENT_CONTACT nvarchar(75),

@CITY nvarchar(75),

@STATE nvarchar(75),

@COUNTRY nvarchar(75),

@SUPPLIER nvarchar(75),

@WORK_ORDER nvarchar(75),

@RIG_NAME nvarchar(75),

@WELL_NAME nvarchar(75),

@OCSG_NUMBER nvarchar(75),

@AFE_NUMBER nvarchar(75),

@MGMT varchar(6000),

@Complete varchar(3),

@newReportId int output,

@changeCommitDate bit = 1

)

AS

SET NOCOUNT ON;

if @changeCommitDate = 1

set @COMMIT_DATE = getdate()

INSERT INTO [reports] ([RPT_ID], [REPORT_DATE], [COMMIT_DATE], [JOB_NUMBER], [TECH_NAME], [CLIENT_NAME], [CLIENT_CONTACT], [CITY], [STATE], [COUNTRY], [SUPPLIER], [WORK_ORDER], [RIG_NAME], [WELL_NAME], [OCSG_NUMBER], [AFE_NUMBER], [MGMT], [isReportComplete])

VALUES (@RPT_ID, @REPORT_DATE, @COMMIT_DATE, @JOB_NUMBER, @TECH_NAME, @CLIENT_NAME, @CLIENT_CONTACT, @CITY, @STATE, @COUNTRY, @SUPPLIER, @WORK_ORDER, @RIG_NAME, @WELL_NAME, @OCSG_NUMBER, @AFE_NUMBER, @MGMT, @Complete );

declare @reference int, @tmpReportId int

set @tmpReportId = SCOPE_IDENTITY()

set @reference = @tmpReportId * -1


SET NOCOUNT Off;


Update [reports] set [RPT_ID] = @reference where id = @tmpReportId

select @newReportId = RPT_ID

FROM REPORTS

WHERE (ID = @tmpReportId)