Extended stored procedure to implement sequence

Hello,

I want do implement an extended stored procedure to implement an oracle sequence in sql server 2000. I need that because i have a script with more than 75000 lines, full of inner selects (for oracle) of the kind:

insert into table1 (col1_1, col1_2, col1_3)

(select col2_1, col2_2, some_sequence.nextval from table2)

I need to port this script to run in sql server. I have a table to implement sequences, in my sql server db, of the kind:

table mysequence (

sequence_name varchar(100),

sequence_id decimal

)

I need a function that will replace 'some_sequence.nextval' for a function that reads mysequence table AND updates it. What happens is that sql server doesn't let me use a update statement inside a function. Well, I was thinking of a extended stored procedure approach, which would update mysequence table and would be called inside a function. Is that possible? Is there any code in C to implement this kind of thing?

I would appreciate any kind of help!

Thanks,

Henrique Faria

If you can change your query can I ask why you can't change it to just

insert into table1 (col1_1, col1_2)

(select col2_1, col2_2 from table2)

i.e let sql generate the identity value?

|||You could do it using an extended stored procedure. But there are lot of stability, reliability, performance, and scalability issues to consider. Your best bet is to use the identity column in SQL Server to generate the sequential values.sql