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

最新推荐文章于 2025-09-29 11:07:32 发布

转载最新推荐文章于 2025-09-29 11:07:32 发布 · 297 阅读

0 ·

CC 4.0 BY-SA版权

原文链接：http://www.cnblogs.com/chriskwok/archive/2010/03/20/1690421.html

本文介绍了一个复杂的SQL存储过程示例，该过程用于递归地查找与指定标识符相关的所有人员标识符。通过创建临时表并使用递归方法来收集数据，此存储过程展示了如何在SQL中实现复杂的数据检索逻辑。

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