Link to home
Start Free TrialLog in
Avatar of William Domenz
William DomenzFlag for United States of America

asked on

@@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

Avatar of chapmandew
chapmandew
Flag of United States of America image

you might have a trigger on the table that is doing some sort of auditing...instead of @@IDENTITY, use SCOPE_IDENTITY() instead.
Yep so many people get this wrong!
Avatar of William Domenz

ASKER

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)
ASKER CERTIFIED SOLUTION
Avatar of chapmandew
chapmandew
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
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/