Hi robprell,

Thanks for the reply.

After some help in this forum, I figured out how to do the counter. My only problem now is to make sure that I will not have a concurrency issue using the code above.

I also would like to understand what a deadlock is?

Cheers

 

That's the problem Jeff... no body pays attention to that little nuance...

--Jeff Moden

  No... you have an IDENTITY column... why are you using a SEQUENCE table like this? Do an insert from your GUI and use SCOPE_IDENTITY to find what the ID of the new row is.

--Jeff Moden

Hi Jeff,

I think my explanation was not clear. I do not need to touch the IDENTITY field at all. This is used to give the company an unique id.

What I am working towards is to build a counter of insertions (but not of companies) which will be store in the same table.

So, for example, before any user from company with IDENTITY 1 makes an advertisement insertion in the adds table, I have to incremente this counter in the company table and return it so that this counter number is inserted in the other table as well.

This has nothing to do with creating IDs.

So, if another user from company 2 makes an add insertion in the adds table, before that I have to go again to the table company increment the counter fiels and get the number back to be inserted together with the add in the add table.

That is why in my stored procedure I am using this sql statement to retrieve the updated counter. Then I will use this number to insert the add in the add table.

UPDATE tblCompany SET @NextON = NextOrderNumber = NextOrderNumber + 1
WHERE CompanyID = @CompanyId

Therefore, I have to make sure this code will not cause any concurrency issues since two users from the same company could make a call at the same time.

After I receive the value from the @NextON output parameter I will use to insert to the add table

Insert into tblAdds (NextCounterNumber, AdDetail, Date)
values( NextON, '...', date)

Please could shed some light?

Cheers,

CPALL