I'm using Delphi5 with MSSQL7, and have a table:
    TABLE1
       YearStr char(4),
        SeqNum integer,
        <some other fields...>

  The primary key is a composite key (YearStr + SeqNum).
  YearStr is the year and SeqNum is a number wich increases with each
new insert (autoincrement). I cannot use IDENTITY property on SeqNum
because I want SeqNum to start automatically from 1 at the beginning of
each year: For example:
  YearStr = 1999, SeqNum = 1,2,3,4,5,....
  YearStr = 2000, SeqNum = 1,2,3,4,5.....

Please, help me, how could I solve this task in transact SQL?

Thanks, Robi.

Sent via Deja.com http://www.deja.com/
Before you buy.

the t-sql statement:
select IsNull(max(seqnum), 0)+1 from table1 where yearstr = SomeValue
will return the next sequence number for a given year, handling the
case where there are no entries for a given year.
you could control inserts to this table, forcing the use of a stored
procedure to perform them:
CREATE PROCEDURE InsertTable1
@yearstr char(4),
@blah1  int,
@blah2 int

as

declare @seqnum int

select @seqnum = 1 + IsNull(max(seqnum), 0) from table1 where
yearstr=@yearstr

insert table1 (yearstr, seqnum, blah1, blah2) values (@yearstr, @seqno,
@blah1, @blah2)
GO

In article <87776h$q7...@nnrp1.deja.com>,

Sent via Deja.com http://www.deja.com/
Before you buy.