?
Solved

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

Posted on 2009-02-19
5
Medium Priority
?
286 Views
Last Modified: 2013-11-07
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

0
Comment
Question by:William
  • 2
  • 2
5 Comments
 
LVL 60

Expert Comment

by:chapmandew
ID: 23681870
you might have a trigger on the table that is doing some sort of auditing...instead of @@IDENTITY, use SCOPE_IDENTITY() instead.
0
 
LVL 19

Expert Comment

by:daveamour
ID: 23681952
Yep so many people get this wrong!
0
 
LVL 11

Author Comment

by:William
ID: 23682062
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
 
LVL 60

Accepted Solution

by:
chapmandew earned 2000 total points
ID: 23682087
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
 
LVL 11

Author Closing Comment

by:William
ID: 31548774
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

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

An alternative to the "For XML" way of pivoting and concatenating result sets into strings, and an easy introduction to "common table expressions" (CTEs). Being someone who is always looking for alternatives to "work your data", I came across this …
One of the most important things in an application is the query performance. This article intends to give you good tips to improve the performance of your queries.
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.

839 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question