INSERT INTO SAVE (Test_ID)
  SELECT MAX(TEST_ID) AS MAXTEST_ID
    FROM TEST
    WHERE TEST_ID NOT IN (SELECT TEST_ID FROM SAVE)

SET @id = SCOPE_IDENTITY() -- Should return the ID inserted into SAVE

use northwind

create table queuestatus (statusid int primary key, statusDesc varchar(25) not null)
go

create table queue 
	(queueID int identity(1,1) primary key clustered
	,queuestatusid int references queuestatus (statusid) not null
	,processerid int not null
	,updateDate datetime not null)
go
create nonclustered index processerid_queueStatusID on queue (processerid, queueStatusid) with fillfactor=80
go

insert queuestatus (statusid,statusDesc)
select 0,'unprocessed' union
select 1,'Reserved' union
select 2,'processed'

set rowcount 100
insert queue (queuestatusid, processerid, updateDate)
select 0, 0, getdate()
from	sysobjects
set rowcount 0

go

create procedure reserveQueueRow
	@processerid int
	,@queueid int output
as
set nocount on
--reserve a row
update queue set
	processerid = @processerid
	,queuestatusid = 1
	,updateDate = getdate()
where	queueID = (select max(queueID) 
		from queue 
		where queuestatusid = 0
		and processerid = 0)
if @@rowcount = 0
	return -1 --all rows processed

--get the id of the reserved row
select @queueID = queueID
from	queue
where	processerid = @processerid
and	queuestatusid = 1

if @queueid is null
	return -2 --problem

return 0 --successfull

go

create proc queueStatus_upd
@queueID int
,@queuestatusID int
as
set nocount on
update queue set
	queuestatusid = 2 --processed
	,updateDate = getdate()
where	queueID = @queueID
go
--------------------------------------------------------------------
--in multiple QA windows:
while 1=1
begin
	declare @qid int
		,@rc int

	exec @rc = reserveQueueRow @processerid = @@spid, @queueid = @qid output
	if @rc = -1
	begin
		raiserror('all rows processed', 10,1)
		break
	end

	if @qid is null
	begin
		raiserror ('some problem', 17,1)
		return
	end

	exec queueStatus_upd @queueID = @qid, @queueStatusID = 2
	--can also add id to another table if you want

	waitfor delay '00:00:00.250'
end
--------------------------------------------------------------------
go
drop proc reserveQueueRow
drop proc queueStatus_upd
drop table queue
drop table queuestatus

quote:

---

Originally posted by zurbum

The problem is that if my store procedure is called from 2 different computers in the same time(not always, but happens), both receive same record, which should not happen.

---

quote:

---

Originally posted by zurbum

INSERT INTO TABLE1 (A,B,C)
SELECT Something1, Something2, (SELECT TOP 1 Something3 FROM (SELECT...JOIN........WHERE don't exist in TABLE1)

---

INSERT INTO CALLED (FORCALL_ID)
SELECT TOP 1 fc.FORCALL_ID 
FROM FORCALL FC 
left join called ex
	on ex.forcall_id = fc.forcall_id
where	ex.forcall_id is null