I have the following table:
create table Words ( Word nvarchar(255) primary key not null ,Popularity int not null )
And the following stored procedure:
CREATE PROCEDURE TrackWord ( @Word nvarchar(255) ) AS BEGIN begin transaction if not exists (select 1 from [dbo].[Words] with (updlock,holdlock,rowlock) where [Word] = @Word ) begin --waitfor delay '00:00:05' insert into [dbo].[Words]( [Word],[Popularity] ) values ( @Word , 1 ) end else begin update [dbo].[Words] set [Popularity] = [Popularity] + 1 where [Word] = @Word end commit transaction END
This works as I expect it to, and I can uncomment the waitfor delay line and run it in different query windows and it works as expected when used from multiple places at the same time.
Now my question is this: Is there is more efficient/elegant way to do this (In SQL 2000)