函数返回表

最新推荐文章于 2021-01-29 00:47:45 发布

YANGZHONGBAO411

最新推荐文章于 2021-01-29 00:47:45 发布

阅读量504

点赞数

本文链接：https://blog.csdn.net/YANGZHONGBAO411/article/details/4558241

版权

调用函数

select @CommunityCode=c.CommunityCode FROM dbo.GetWholeRealPerson() p left JOIN Community c ON c.groupid = p.GroupID WHERE p.PersonID= @PersonID;

------------------------------------------------------------------------相应的函数

RETURNS @Person TABLE(
[PersonID] int null,
[GroupID] int NULL,
[FamilyID] int null,
[GroupName] nvarchar(100) NULL,
[Name] nvarchar(30) null,
[Sex] nvarchar(10) null,
[SexName] nvarchar(10) null,
[BloodType] nvarchar(10) null,
[BloodTypeName] nvarchar(10) null,
[BirthDate] datetime null,
[Age] int null,
[IdentificationCard] nvarchar(50) null,
[FamilyRelationID] nvarchar(10) null,
[FamilyRelationName] nvarchar(10) null,
[Peoples] nvarchar(10) null,
[PeoplesName] nvarchar(20) null,
[Marriage] nvarchar(10) null,
[MarriageName] nvarchar(10) null,
[LinkMan] nvarchar(50) null,
[Degree] nvarchar(10) null,
[DegreeName] nvarchar(10) null,
[Company] nvarchar(50) null,
[Profession] nvarchar(50) null,
[ProfessionName] nvarchar(50) null,
[InputDate] datetime null,
[InputUserID] uniqueidentifier null,
[IsLowIncome] int null,
[PaymentMode] nvarchar(10) null,
[PaymentModeName] nvarchar(10) null,
[PersonPhone] nvarchar(50) null,
[PersonLike] nvarchar(100) null,
[ConsultHealth] int null,
[ChildCount] int null,
[OldCount] int null,
[OldHealth] int null,
[ExposureHistory] int null,
[ExposureRemark] nvarchar(50) null,
[IsHeredity] int null,
[HeredityRemark] nvarchar(200) null,
[PersonState] nvarchar(10) null,
[PersonStateName] nvarchar(10) null,
[CardCode] nvarchar(50) null,
[EnterDate] datetime null,
[EnterCause] nvarchar(200) null,
[DwellType] nvarchar(10) null,
[DwellTypeName] nvarchar(10) null,
[PersonCode] nvarchar(50) null,
[InActive] int null,
[InsuranceBargainCode] nvarchar(30) null,
[CommunityInsuranceCode] nvarchar(30) null,
[IatricalHospital] nvarchar(50) null,
[CompanyPhone] nvarchar(30) null,
[PersonAddress] nvarchar(100) null,
[LongAddress] nvarchar(100) null,
[RegisterAddress] nvarchar(30) null,
[OtherAddress] nvarchar(4000) NULL,
[MedicalInSuranceCode] nvarchar(30) null,
[OtherRelation] nvarchar(50) null,
[IatricInsuranceModel] nvarchar(10) null,
[IatricInsuranceModelName] nvarchar(10) null,
[DwellDate] datetime null,
[CertificateType] nvarchar(10) null,
[CertificateTypeName] nvarchar(10) null,
[CardCode2] nvarchar(50) null,
[OldCommity] nvarchar(100) null,
[Hukou] nvarchar(100) null
)
AS
BEGIN
INSERT INTO @Person
SELECT
  [PersonID],
  Person.[GroupID],
  Person.[FamilyID],
        [GroupName]=(select GroupName from Admin_Group where GroupID=Person.GroupID),
  [Name],
  [Sex],
  dbo.ExplainStandard([Sex],10),
  [BloodType],
  dbo.ExplainStandard([BloodType],12),
  ( CASE [BirthDate]
   WHEN NULL THEN NULL
   ELSE CONVERT(varchar(10),[BirthDate],23)
   END
  ),
  DateDiff(Year,[BirthDate],GetDate()),
  [IdentificationCard],
  [FamilyRelationID],
  dbo.ExplainStandard([FamilyRelationID],14),
  [Peoples],
  dbo.ExplainStandard([Peoples],11),
  [Marriage],
  dbo.ExplainStandard([Marriage],57),
  [LinkMan],
  [Degree],
  dbo.ExplainStandard([Degree],46),
  [Company],
  [Profession],
  dbo.ExplainStandard([Profession],68),
  Person.[InputDate],
  [InputUserID],
  [IsLowIncome],
  [PaymentMode],
  dbo.ExplainStandard([PaymentMode],58),
  [PersonPhone],
  [PersonLike],
  [ConsultHealth],
  [ChildCount],
  [OldCount],
  [OldHealth],
  [ExposureHistory],
  [ExposureRemark],
  [IsHeredity],
  [HeredityRemark],
  [PersonState],
  dbo.ExplainStandard([PersonState],6),
  [CardCode],
  [EnterDate],
  [EnterCause],
  [DwellType],
  dbo.ExplainStandard([DwellType],13),
  [PersonCode],
  Person.[InActive],
  [InsuranceBargainCode],
  [CommunityInsuranceCode],
  [IatricalHospital],
  [CompanyPhone],
  [PersonAddress],
  [LongAddress],
  [RegisterAddress],
  (SELECT TOP 1 OtherAddress FROM Family LEFT JOIN Address ON Address.AddressID = Family.AddressID WHERE Person.FamilyID = Family.FamilyID) AS OtherAddress,
  [MedicalInSuranceCode],
  [OtherRelation],
  [IatricInsuranceModel],
  dbo.ExplainStandard([IatricInsuranceModel],66),
  [DwellDate],
  [CertificateType],
  dbo.ExplainStandard([CertificateType],67),
  [CardCode2],
  [OldCommity],
  [Hukou]
  FROM [Person]
  INNER JOIN Family ON Family.FamilyID = [Person].FamilyID AND Family.Inactive = 0 AND Family.IsMoveOut = 0
WHERE --PersonState IN (0,1,4) AND
Person.InActive = 0
    RETURN
END