I have started to re-write a stored proc that used to use a cursor. To avoid using the cursor, I've made the insert statement that is the primary function of the code set based. My big problem though is that the primary key that I am inserting is taken from another table and incremented for each entry and more importantly, the table that stores the key is also updated so that the key is not used again. This is where the problem lies. Because it is set based rather than a loop, I can't update as I go. I tried using a UDF to get the number, but these can't update, only select, so I am a bit stuck. Is my only option to use extended stored procs, a DTS job or to revert to using a while loop? What about using a before insert trigger and adding the primary key that way? Just after thoughts really!
insert into docsadm.needs_indexing(system_id, status, status_date, retry_count, docnumber, ft_system_id) select dbo.fnc_GetKey(), 1, getdate(),0,docnumber,@ftsystem_id from docsadm.profile where LDA_PROCESSED='N' and fulltext='Y' and convert(varchar,creation_time ,108) < @createdate