Database

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

Sonia Gupta wrote:

is there any table like datatype available?

Declare @table_name Table(column_name1 datatype,column_name2 datatype)