Sequences without Identity

Hello

I have the following statement

INSERT INTO TableA (OID, ReferenceField)
SELECT MyOID, <Sequence>, DependantField, Otherfields
FROM TableB

I need the sequence to be made up of the current date and a number held in another table which should be updated each time its called. The sequence numbers are held in a table and are dependant on the additional field "DependantField")
e.g.
Sequence table
SEQUENCENAME(DependantField) VALUE
ABC 1
CDE 1000

Ideally I wanted a function which would take in the dependant field and would return the next in the sequence while updating the sequence table e.g
INSERT INTO TableA (OID, ReferenceField)
SELECT MyOID, UFN_fnGetNextSequence(DependantField), DependantField, Otherfields
FROM TableB

But I cant include an update statement within the function.

Any help much appreciated,

PS If even you can give me some code to increment a counter while inserting without using identity fields.

Thanks

You cannot update a table inside a functino. You could use a stored procedure to do it.
Write a store procedure that gets you the next value and increments it.

Suppose this is your sequence table

CREATE TABLE SEQUENCE_TABLE (SEQUENCE_NAME varchar(20), SEQUENCE_VALUE int)

INSERT INTO SEQUENCE_TABLE select 'ABC', 1 UNION ALL select 'CDE', 1000

CREATE PROCEDURE GetNextID
@SEQUENCE_NAME varchar(20),
@SEQUENCE_VALUEint OUTPUT
AS
SET NOCOUNT ON

SET @SEQUENCE_VALUE = NULL

-- first asign the value to the variable, then increment the value in table.
UPDATE SEQUENCE_TABLE
SET @SEQUENCE_VALUE = SEQUENCE_VALUE = SEQUENCE_VALUE + 1
WHERE SEQUENCE_NAME = @SEQUENCE_NAME

IF (@@ROWCOUNT <> 1) OR (@@ERROR <> 0)
RETURN -1 -- error
RETURN 0 -- ok

-- This is the call to the procedure.
DECLARE @nextID int, @RC int
exec @rc = GetNextID 'ABC', @nextID OUTPUT
SELECT @RC, @nextID
Just add to the code the current date.

Bambola.

I didn't know you could do this...?

-- first asign the value to the variable, then increment the value in table.
UPDATE SEQUENCE_TABLE
SET @SEQUENCE_VALUE = SEQUENCE_VALUE = SEQUENCE_VALUE + 1
WHERE SEQUENCE_NAME = @SEQUENCE_NAME

two equal signs in one set line...? very cunning!!!

Cheers
Twan

Isn't that cool? I use it in a certain situation when we cannot use identity and it works like a charm!

Bambola.

Interesting. And if the requirment is only to generate a seq number it would work. Keep in mind that 1- It would cost you a join on each select 2- you can only do it if you have a unique column or a combinations of columns that makes a unique value.
I was under the impression that what hmckillop needs is a number that together with the date would server as a key. Maybe I was wrong.

Bambola.