Solved

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

Posted on 2011-03-11
6
880 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
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 69

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

Microsoft Certification Exam 74-409

VeeamĀ® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Union 2 queries to a cte (temp table perhaps) 9 41
sql help 2 45
Loops and updating in SQL Query 9 53
too many installs coming along with SQL 2016? 1 16
In this article I will describe the Detach & Attach method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
In this article I will describe the Backup & Restore method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.

856 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