update myMetaTable set 
	[InUse] = 1 
where [InUse] = 0

if @@rowcount = 0
begin
	Raiserror ('someone else is executing the procedure now', 17,1)
	return -1
end

---After all processing
update myMetaTable set 
	[InUse] = 0

create procedure oneInstance as

if exists(select * from master.dbo.sysprocesses where context_info = 0x0666)
begin
	raiserror('I''m already running, sorry!',16,1)
	return 50000
end

set context_info 0x0666
waitfor delay '00:00:05' -- wait 5 seconds

select 'I''m done'
set context_info 0x00

quote:

---

Originally posted by Kristen

Hi ceciliarenebaker, Welcome to SQL Team!

We tend to do this type of job by having a "batch number" column in the table. At the start of the operation we set all the rows we are going to process with the next batch number, this in turn prevents other processes from operating on the same rows.

Then we process all of the rows that are in "our batch"

We write other processes to be aware of the batch number system, or use a status of "Being processed" that prevented other operations messing with the records (i.e. assuming the table has a Status field, then use a status code to indicate the rows are only available for reading, or not at all).

If the batch goes wrong (email didn't get through, printer jammed, or somesuch) we can then "reprocess" that batch (which usually means just the re-print part, rather than the bit that pays out the money again!)

Kristen

---