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]
@ChatRoomId nvarchar(255) OUTPUT
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
INSERT INTO ChatRoom (CreatorId) VALUES (@SenderId);
SELECT @ChatRoomId = @@IDENTITY;
IF @ChatRoomId IS NOT NULL
INSERT INTO ChatRoomGuests (ChatRoomId,UserId) VALUES (@ChatRoomId,@RecipientId);
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?