Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

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

Posted on 2011-03-11
6
Medium Priority
?
961 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 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 71

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

 [eBook] Windows Nano Server

Download this FREE eBook and learn all you need to get started with Windows Nano Server, including deployment options, remote management
and troubleshooting tips and tricks

Question has a verified solution.

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

If you have heard of RFC822 date formats, they can be quite a challenge in SQL Server. RFC822 is an Internet standard format for email message headers, including all dates within those headers. The RFC822 protocols are available in detail at:   ht…
How to leverage one TLS certificate to encrypt Microsoft SQL traffic and Remote Desktop Services, versus creating multiple tickets for the same server.
Please read the paragraph below before following the instructions in the video — there are important caveats in the paragraph that I did not mention in the video. If your PaperPort 12 or PaperPort 14 is failing to start, or crashing, or hanging, …
In a question here at Experts Exchange (https://www.experts-exchange.com/questions/29062564/Adobe-acrobat-reader-DC.html), a member asked how to create a signature in Adobe Acrobat Reader DC (the free Reader product, not the paid, full Acrobat produ…

971 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