I have a table called DUTY (columns: dutyid, dutyname, staffid) and a table called STAFF (columns: staffid, staffname)

In order to be fair, each of the staff will be auto assigned to each duty entry (record). So what should I do whenever I would like to insert a duty entry, it will auto assign the staffid (sequential) for it.


  • staffid : 1 staffname: Jack
  • staffid : 2 staffname: Mary

So when I insert a new entry (first entry) for duty, it will auto insert staffid = 1 for duty table. For second entry, staffid will be 2.

And for the following entry, it will keep looping the staffid sequentially.

Desired answer:

dutyid   dutyname   staffid 
  1      cleaning     1 
  2      cleaning     2 
  3      cleaning     1 
  4      cleaning     2 
  5      cleaning     1 
  6      cleaning     2 
  7      cleaning     3       new staff
  8      cleaning     1 
  9      cleaning     2 
 10      cleaning     3 

Can anyone show and explain to me what I should do in my stored procedure...

First get the last dutyid record. That holds the last used staffid. You can then use that to retrieve the next staffid.

Be a part of the DaniWeb community

We're a friendly, industry-focused community of 1.21 million developers, IT pros, digital marketers, and technology enthusiasts learning and sharing knowledge.