不确定这能解决问题
但是你真的需要
WHERE m.ID = @@IDENTITY
IF NOT EXISTS(select top(1) id from [eddsdbo].[Things] (UPDLOCK)
where [ThingTypeID] = @thingTypeID and [HourID] = @hourID)
BEGIN
INSERT INTO [eddsdbo].[Things]
([ThingTypeID], [HourID])
VALUES (@thingTypeID, @hourID)
END
SELECT m.*, mt.SampleType
FROM [eddsdbo].[Things] as m
inner join [eddsdbo].[ThingTypes] as mt
on mt.Id = m.ThingTypeID
and [ThingTypeID] = @thingTypeID
and [HourID] = @hourID
单个语句是一个事务
我想这会减少开销
DECLARE @t AS TABLE (id int identity primary key, thingTypeID int, hourID int);
declare @thingTypeID int = 1, @hourID int = 2;
insert into @t (thingTypeID, hourID)
values (@thingTypeID, @hourID);
select *
from @T
where thingTypeID = @thingTypeID and hourID = @hourID;
insert into @t (thingTypeID, hourID)
select @thingTypeID, @hourID
where not exists (select 1 from @t where thingTypeID = @thingTypeID and hourID = @hourID);
select *
from @T
where thingTypeID = @thingTypeID and hourID = @hourID;
set @thingTypeID = 1;
set @hourID = 3;
insert into @t (thingTypeID, hourID)
select @thingTypeID, @hourID
where not exists (select 1 from @t where thingTypeID = @thingTypeID and hourID = @hourID);
select *
from @T
where thingTypeID = @thingTypeID and hourID = @hourID;
select *
from @T
order by id;