Saturday 14 September 2013

SQL SCOPE_IDENTITY Recording twice

SQL SCOPE_IDENTITY Recording twice

I have a stored procedure as
@user1 uniqueidentifier ,
@user2 uniqueidentifier ,
@mesaj_text varchar(500),
@mesaj_read int
AS
BEGIN
declare @chat_id int
IF EXISTS (SELECT chat_id FROM chat where user1=@user1 and user2 =@user2
or user1=@user2 and user2 =@user1)
BEGIN SELECT @chat_id =chat_id FROM chat where user1=@user1 and
user2 =@user2 or user1=@user2 and user2 =@user1
INSERT INTO mesaj values
(@chat_id,@user1,@mesaj_text,getdate(),@mesaj_read) END
ELSE insert into chat values (@user1,@user2)
SET @chat_id=SCOPE_IDENTITY()
insert into mesaj values
(@chat_id,@user1,@mesaj_text,getdate(),@mesaj_read)
Everything is ok.
But recording twice "mesaj".Getting first inserted id of "mesaj" and using
it as chat_id for second record. So recording twice.(mesaj means message
in Turkish :) ) .I tried to use @@IDENTITY instead of SCOPE_IDENTITY but
nothing change.I tried to use IDENT_CURRENT(chat) but return error
"Invalid column name 'chat'."
How can I solve this problem ?

No comments:

Post a Comment