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