@@IDENTITY returns same ID to 2 different callers....

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
BillyDVd
USE [CustomerIssueDatabase]
GO
 
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:		
-- Create date: 
-- Description:	Insert New Problem
-- =============================================
ALTER PROCEDURE [dbo].[InsertNewProblem] 
	-- Add the parameters for the stored procedure here
			@CustomerID int,
			@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,
			@TechSupRepID int,
			@Suggestion bit = null,
			@ID int OUTPUT
	
AS
BEGIN
	-- 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;
    ELSE
    -- Procedure must start its own transaction.
BEGIN TRANSACTION;
    -- Insert statements for procedure here
	BEGIN TRY
	INSERT INTO CustomerProblem
		(	CustomerID ,
			DateClosed ,
			DateElapsed ,
			EngVin ,
			HotLine ,
			LiveCall,
			CallTypeID ,
			CategoryID ,
			Regarding,
			Complaint,
			[Action] ,
			Comments ,
			TechSupRepID ,
			Suggestion,
			Active,
			LastUpdated
			)
	VALUES
		 (
			@CustomerID,
			@DateClosed ,
			@DateElapsed ,
			@EngVin ,
			@HotLine ,
			@LiveCall ,
			@CallTypeID,
			@CategoryID ,
			@Regarding ,
			@Complaint ,
			@Action ,
			@Comments,
			@TechSupRepID ,
			@Suggestion ,
			1,
			GETDATE()
		);
--|=====================	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.
        COMMIT TRANSACTION;
		SET @ID = @@IDENTITY
	--| Returns the pkid for the output .. |
--|=====================	END TRY	 =========================|--
	END TRY
--|====================================================================================|--
	BEGIN CATCH
		-- 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.
			ROLLBACK TRANSACTION;
        ELSE
        -- 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.
                   );
	END CATCH
--|====================================================================================|--
END

Open in new window

LVL 11
WilliamAsked:
Who is Participating?
 
chapmandewConnect With a Mentor Commented:
Sure.  So, when you insert a record into a table that has an iD column, @@IDENTITY returns the last ID value inserted for the session.  If there is a trigger and it inserts an ID value, @@IDENTITY will return that value instead.  SCOPE_IDENTITY() will not...make sense?

Yes, I almost always use SCOPE_IDENTITY()
0
 
chapmandewCommented:
you might have a trigger on the table that is doing some sort of auditing...instead of @@IDENTITY, use SCOPE_IDENTITY() instead.
0
 
daveamourCommented:
Yep so many people get this wrong!
0
 
WilliamAuthor Commented:
There are zero triggers in this database.
Without triggers do you still use SCOPE_IDENTITY()?
If so ...  why?(If you can give a little info, I would like to really understand the differece, as all the reading seems to talk about triggers)
0
 
WilliamAuthor Commented:
Thanks for the quick response and straight answer :)
I was reading this when you posted so for the others who ever read this can get even more info if needed....   http://www.sqlservercentral.com/articles/Miscellaneous/checkyoursqlserveridentity/1993/
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.