获取患者主索引的所有指定域的患者标识

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

 

转载于:https://www.cnblogs.com/chriskwok/archive/2010/03/20/1690421.html