In certain instances, you may find that theere is a defect in LinkLogic, or you may have another reason for doing this, but for what it’s worth, there may come a time when you need to insert a row into a Centricity database that has a unique ID field.
There is a built-in stored procedure that performs the task, but it takes an input parameter, which can make it tricky to call. You can either create a wrapper function for the procedure that declares an input parameter and returns the value or you can copy the code and remove the params altogether.
I have taken the second approach so that you can see the logic of the full procedure.
/****** Object: StoredProcedure [dbo].[CUSTOMgetUniqueEMRID] Script Date: 05/05/2013 23:05:12 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO -- CUSTOM_getUniqueEMRID -- returns a generated emrid as numeric(19,0) -- NOTE: dependent on emridcontrol table being setup and setupEMRIDControl having been called CREATE procedure [dbo].[CUSTOM_getUniqueEMRID] as SET NOCOUNT ON begin declare @theID numeric(19,0) declare @theOffset numeric(19,0) declare @theLastID numeric(19,0), @theWSID numeric(19,0) declare emridcontrolcursor cursor forward_only for select lastid,wsid from dbo.emridcontrol for update of lastid set @theID=0 set @theOffset=50000 open emridcontrolcursor fetch next from emridcontrolcursor into @theLastID, @theWSID if @@fetch_status = 0 begin set @theID = dbo.convert_date_to_id(getdate()) + @theWSID if @theID <=@theLastID set @theID = @theLastID+@theOffset update dbo.emridcontrol set LastID=@theID end else RAISERROR('unable to read emridcontrol table',10,1) close emridcontrolcursor deallocate emridcontrolcursor select @theID as getUniqueEMRID end