Database

Xmen wrote:

it will be strange that if someone make a table only for a string

SELECT *<br />
FROM<br />
    (SELECT ColP, ColQ, ColR FROM TableA)a<br />
INNER JOIN<br />
    (SELECT ColS, ColT, ColU FROM TableA)b<br />
ON a.ColP = b.ColS;

create procedure dbo.GetNextAvailable
as begin
  declare @id int
  set nocount on

  --Start transaction so that locking works.
  begin tran

  --Get Id of next record to process.  The "holdlock" hint tells SQL
  --Server to place a lock on the row until the end of the transaction.
  --The "readpast" hint tells SQL-Server to ignore a record if it is
  --locked by one of your other two processes. Note that this only works
  --for "read committed" isolation.
  select top 1 @id = MyId from MyTable with (holdlock, readpast)
    where status = 'READY'
    order by MyId

  --Mark the record as "in-progress" so that no-one else can pick-up
  --the current record.
  update MyTable set status = 'IN PROGRESS'
    where MyId = @id
    and status = 'READY'

  --Complete the transaction.
  commit trans

  --Return the record to the front-end for processing.  If no records
  --returned then there are no records left to process.
  select * from MyTable where MyId = @id

  return(0)
end