?
Solved

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

Posted on 2011-03-11
6
Medium Priority
?
977 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
5 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 1000 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 1000 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 72

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

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

It is possible to export the data of a SQL Table in SSMS and generate INSERT statements. It's neatly tucked away in the generate scripts option of a database.
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.
SQL Database Recovery Software repairs the MDF & NDF Files, corrupted due to hardware related issues or software related errors. Provides preview of recovered database objects and allows saving in either MSSQL, CSV, HTML or XLS format. Ensures recov…
Stellar Phoenix SQL Database Repair software easily fixes the suspect mode issue of SQL Server database. It is a simple process to bring the database from suspect mode to normal mode. Check out the video and fix the SQL database suspect mode problem.

621 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