I have this stored procedure that I want to return the LAST PKID for the table it has inserted a new record into...
This works, however I have had issues where 2 different users have the same ID returned to them after calling this....
Why / How can/could this be hapening?
THis is a web application calling the db.... Do not know if this matters...
Thanks in advance
SET ANSI_NULLS ON
SET QUOTED_IDENTIFIER ON
-- Create date:
-- Description: Insert New Problem
ALTER PROCEDURE [dbo].[InsertNewProblem]
-- Add the parameters for the stored procedure here
@DateClosed DateTime = null,
@DateElapsed DateTime = null,
@EngVin varchar(50) = null,
@HotLine bit = null,
@LiveCall bit = null,
@CallTypeID int = 1,
@CategoryID int = 1,
@Regarding varchar(max) = null,
@Complaint varchar(max) = null,
@Action varchar(max) = null,
@Comments varchar(max) = null,
@Suggestion bit = null,
@ID int OUTPUT
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
DECLARE @TranCounter INT;
SET @TranCounter = @@TRANCOUNT;
IF @TranCounter > 0
-- Procedure called when there is an active transaction.
-- Create a savepoint to be able to roll back only the work done in the procedure if there is an error.
SAVE TRANSACTION ProcedureSave;
-- Procedure must start its own transaction.
-- Insert statements for procedure here
INSERT INTO CustomerProblem
( CustomerID ,
--|===================== END SQL SATEMENT =======================|--
-- Get here if no errors; must commit any transaction started in the procedure, but not commit a
-- transaction started before the transaction was called.
IF @TranCounter = 0
-- @TranCounter = 0 means no transaction was started before the procedure was called.
-- The procedure must commit the transaction it started.
SET @ID = @@IDENTITY
--| Returns the pkid for the output .. |
--|===================== END TRY =========================|--
-- An error occurred somewhere in the try block.
-- An error occurred; must determine which type of rollback will roll
-- back only the work done in the procedure.
IF @TranCounter = 0
-- Transaction started in procedure.
-- Roll back complete transaction.
-- Transaction started before procedure called, do not roll back modifications made before the procedure was called.
IF XACT_STATE() <> -1
-- If the transaction is still valid, just roll back to the savepoint set at the start of the stored procedure.
ROLLBACK TRANSACTION ProcedureSave;
-- If the transaction is uncommitable, a rollback to the savepoint is not allowed because the savepoint rollback writes to the log.
-- Just return to the caller, who should roll back the outer transaction.
-- Notify the client by raising an exception with the error details.
-- After the appropriate rollback, echo error information to the caller.
DECLARE @ErrorMessage NVARCHAR(4000);
DECLARE @ErrorSeverity INT;
DECLARE @ErrorState INT;
SELECT @ErrorMessage = ERROR_MESSAGE();
SELECT @ErrorSeverity = ERROR_SEVERITY();
SELECT @ErrorState = ERROR_STATE();
RAISERROR (@ErrorMessage, -- Message text.
@ErrorSeverity, -- Severity.
@ErrorState -- State.