Solved

Why @@IDENTITY returns null after a new row with uniqueidentifier is added?

Posted on 2011-03-11
6
912 Views
Last Modified: 2012-05-11
Using SQL Server 2008. Created a stored procedure that uses the identity of a newly inserted row as data for later inserts. However, @@IDENTITY and SCOPE_IDENTITY() both return NULL even though I verify that the row was created.

Here is the stored procedure:

CREATE PROCEDURE [dbo].[MakeConnection]
      @SenderId nvarchar(255),
      @RecipientId nvarchar(255),
      @ChatRoomId nvarchar(255) OUTPUT
AS
BEGIN
      SET NOCOUNT ON;
      
      SELECT @ChatRoomId=cr.ChatRoomId FROM ChatRoom cr
            JOIN ChatRoomGuests g ON g.ChatRoomId = cr.ChatRoomId
            WHERE (cr.CreatorId = @SenderId AND g.UserId = @RecipientId )
            OR (cr.CreatorId = @RecipientId AND g.UserId = @SenderId);

      IF @ChatRoomId IS NULL
      BEGIN
            INSERT INTO ChatRoom (CreatorId) VALUES (@SenderId);
            SELECT @ChatRoomId = @@IDENTITY;
            IF @ChatRoomId IS NOT NULL
            BEGIN
                  INSERT INTO ChatRoomGuests (ChatRoomId,UserId) VALUES (@ChatRoomId,@RecipientId);
            END
      END
END


The first INSERT INTO (ChatRoom) is followed by the assignment of @@IDENTITY to the variable @ChatRoomId. I have verified that @ChatRoomId and @@IDENTITY come back as NULL.

I suspect that this is because ChatRoom, the table, does not have an identity column. This is the schema for ChatRoom:

CREATE TABLE [dbo].[ChatRoom](
      [ChatRoomId] [uniqueidentifier] NOT NULL,
      [Name] [nvarchar](255) NULL,
      [CreatorId] [nvarchar](255) NOT NULL,
      [Created] [datetime] NOT NULL,
      [Private] [bit] NOT NULL
) ON [PRIMARY]

I do not know how to set the ChatRoomId column to be the identity column. This seems to be disallowed by SS Management Studio, I'm guessing because the column is a uniqueidentifier type.

How do I get the value of the newly created row back, when in this case @@IDENTITY returns NULL?
0
Comment
Question by:lynnjwalker
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
6 Comments
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 35106417
please use SCOPE_IDENTITY instead of @@IDENTITY
0
 
LVL 9

Accepted Solution

by:
kaminda earned 250 total points
ID: 35107253
TheChatRoomId in ChatRoom table is not an Identity column. It is a GUID column as per your table definition. You can use @@identity if the table has an identity column. This is why you are getting a null.
0
 
LVL 6

Assisted Solution

by:jonaska
jonaska earned 250 total points
ID: 35107976
The part: INSERT INTO ChatRoom (CreatorId) VALUES (@SenderId); should not succeed.
You're not supplying all the necessery values for the table?!
You could use the NEWID() funtion to generate a new guid value.
0
 
LVL 21

Expert Comment

by:Alpesh Patel
ID: 35108392
Please check column is identity
0
 
LVL 70

Expert Comment

by:Qlemo
ID: 36032394
This question has been classified as abandoned and is closed as part of the Cleanup Program. See the recommendation for more details.
0

Featured Post

Instantly Create Instructional Tutorials

Contextual Guidance at the moment of need helps your employees adopt to new software or processes instantly. Boost knowledge retention and employee engagement step-by-step with one easy solution.

Question has a verified solution.

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

SQL Server engine let you use a Windows account or a SQL Server account to connect to a SQL Server instance. This can be configured immediatly during the SQL Server installation or after in the Server Authentication section in the Server properties …
Composite queries are used to retrieve the results from joining multiple queries after applying any filters. UNION, INTERSECT, MINUS, and UNION ALL are some of the operators used to get certain desired results.​
In this video, viewers will be given step by step instructions on adjusting mouse, pointer and cursor visibility in Microsoft Windows 10. The video seeks to educate those who are struggling with the new Windows 10 Graphical User Interface. Change Cu…
This tutorial will teach you the special effect of super speed similar to the fictional character Wally West aka "The Flash" After Shake : http://www.videocopilot.net/presets/after_shake/ All lightning effects with instructions : http://www.mediaf…

623 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