Improve company productivity with a Business Account.Sign Up

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1021
  • Last Modified:

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

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
lynnjwalker
Asked:
lynnjwalker
2 Solutions
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
please use SCOPE_IDENTITY instead of @@IDENTITY
0
 
kamindaCommented:
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
 
jonaskaCommented:
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
 
Alpesh PatelAssistant ConsultantCommented:
Please check column is identity
0
 
QlemoBatchelor, Developer and EE Topic AdvisorCommented:
This question has been classified as abandoned and is closed as part of the Cleanup Program. See the recommendation for more details.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

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.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now