hey all,

i have a transaction table i need to add a record to. the primary key is a 2
field column. RecordID and then the SequenceID.

How i understand it is:
To add a new record i have to
1. Find the last sequence number used
2. Then add the new record

can i do this in one stored procedure?

thanks,
rodchar

Why not use an IDENTITY column?


--
Adam Machanic
SQL Server MVP
http://www.datamanipulation.net
--


"rodchar" <rodchar (AT) discussions (DOT) microsoft.com> wrote

Again provide DDL...

Create Table (RecordId Int, SequenceId Int
, Constraint Primary Key (RecordId, SequenceId))

Insert Table(RecordId, SequenceId)
Select RecordId, Max(SequenceId) + 1
From Table
Group By RecordId

BTW, this does have issues in a multi-user environment. If two people were to
execute this function at exactly the same time, they'll get the same answer and
thus a collision. A better way would be to make a small table that stores the
last value used.


Thomas



"rodchar" <rodchar (AT) discussions (DOT) microsoft.com> wrote

"Thomas" <thomas (AT) newsgroup (DOT) nospam> wrote

...which would have the same issue -- what would stop two readers from
getting the value simultaneously?


--
Adam Machanic
SQL Server MVP
http://www.datamanipulation.net
--

The problem is that this is an existing table in production.

"Adam Machanic" wrote:

Just increase Isolation Leel to Repeatable Read (or Serializeable) to prevent
this issue from arising....

"Thomas" wrote:

If you use a small table that stores the next value, you can lock the table and
increment the "next" value. In essence, serializing the retrieval of the next id
value. However, it does mean you may get gaps.


Thomas


"Adam Machanic" <amachanic (AT) hotmail (DOT) _removetoemail_.com> wrote

If you can withstand the entire table being locked during the insert process,
this would also be a viable choice.


Thomas

"CBretana" <cbretana (AT) areteIndNOSPAM (DOT) com> wrote

"CBretana" <cbretana (AT) areteIndNOSPAM (DOT) com> wrote

How would that prevent issues?

QA Window 1:
----------------

use tempdb
go

create table x(id int)
go

insert x values (1)
go

set transaction isolation level serializable
go

begin tran

select id
from x
go



QA Window 2:
---------------
use tempdb
go

set transaction isolation level serializable
go

begin tran

select id
from x
go


Serializable blocks only if writes have taken place.


--
Adam Machanic
SQL Server MVP
http://www.datamanipulation.net
--

Adam,

Yes with just a read, but if you use the relation from t he select as
Insert values, you are doing more than just a read, and qry window 2 will
block..

create table x(id int)
go

insert x values (1)
go

set transaction isolation level serializable
go

begin tran

Insert x (id)
select id + 1 from x


-- Wait here while you run Qry WIndow 2 -----

Commit Tran
-- ******************************


--Query Window 2
---------------------------
set transaction isolation level serializable
go

begin tran

Insert x (id)
select id + 1 from x
-- Now go back and commit Query Window 1
-- -------------------------------------------------
"Adam Machanic" wrote: