获取患者主索引的所有指定域的患者标识
ALTER PROCEDURE [dbo].[pGetPersonIdentifiers](
@identifierRootId int, @identifier varchar(100), @personId int = -1
)
AS
set nocount on
BEGIN TRY
IF NOT EXISTS (SELECT * FROM [tempdb].sys.objects WHERE name = '##PersonIdentifier')
CREATE TABLE ##PersonIdentifier(
[PersonId] [int] ,
[IdentifierRootID] [int] ,
[Identifier] [varchar](100) NOT NULL
)
IF NOT EXISTS (SELECT * FROM [tempdb].sys.objects WHERE name = '##relativePersonId')
CREATE TABLE ##relativePersonId(
[RID] [int] identity(1,1),
[RelatedPersonId] [int] ,
[IsHandled] [bit] NOT NULL default 0
)
if @personId = -1
SELECT @personId = PersonId
FROM PersonIdentifier with(nolock) WHERE IdentifierRootId = @identifierRootId AND Identifier = @identifier
INSERT ##PersonIdentifier(PersonId, IdentifierRootId, Identifier)
SELECT PersonId, IdentifierRootId, Identifier
FROM PersonIdentifier with(nolock) WHERE IdentifierRootId = @identifierRootId AND PersonId = @personId
INSERT ##relativePersonId(RelatedPersonId)
SELECT convert(int, Identifier)
FROM PersonIdentifier with(nolock)
WHERE IdentifierRootId = 100 AND PersonId = @personId AND Identifier <> @identifier
and convert(int, Identifier) not in (select RelatedPersonId from ##relativePersonId)
INSERT ##relativePersonId(RelatedPersonId)
SELECT PersonId
FROM PersonIdentifier with(nolock)
WHERE PersonId <> @personID AND IdentifierRootId = 100 AND Identifier = convert(varchar(100), @personId)
and PersonId not in (select RelatedPersonId from ##relativePersonId)
DECLARE @relatedPersonId int, @rid int
declare @relatedPersonIdentifier varchar(100)
SET @relatedPersonIdentifier = convert(varchar(100), @personId)
SELECT TOP 1 @rid = RID, @relatedPersonId = RelatedPersonId FROM ##relativePersonId WHERE IsHandled = 0
IF @relatedPersonId IS NOT NULL
begin
update ##relativePersonId set IsHandled = 1 where RID = @rid
exec [dbo].[pGetPersonIdentifiers] @identifierRootId, @relatedPersonIdentifier, @relatedPersonId
end
ELSE
begin
SELECT * FROM ##PersonIdentifier
DROP TABLE ##PersonIdentifier
DROP TABLE ##relativePersonId
end
END TRY
BEGIN CATCH
IF EXISTS (SELECT * FROM [tempdb].sys.objects WHERE name = '##PersonIdentifier')
DROP TABLE ##PersonIdentifier
IF EXISTS (SELECT * FROM [tempdb].sys.objects WHERE name = '##relativePersonId')
DROP TABLE ##relativePersonId
END CATCH
set nocount off