各位老大,我觉得我写得太笨了.有没有办法优化了一下下啊!

Question

create    PROCEDURE GetFamilyPermissionOfFamilyByUserId
(
@Userid int ,
@Familyid int
)

AS
/*首先判断他是否是家主*/
 if(exists(select domainName,familyId,creator,creatorid,sitetitle,0 as MemberPermission from familyProfile where creatorid=@Userid and familyId=@familyId))
 begin 
   select domainName,familyId,creator,creatorid,sitetitle,0 as MemberPermission from familyProfile where creatorid=@Userid and familyId=@familyId
   return 
 end
/*判断它是否是家庭中的成员权限*/
if(exists(select domainName,familyprofile.familyId,creator,creatorid,sitetitle,1 as MemberPerssion from familyProfile  inner  join member on member.familyId=familyprofile.FamilyId where userid=@Userid and approval=1 and auditing=0 and familyprofile.FamilyId=@familyId))
begin
  (select domainName,familyprofile.familyId,creator,creatorid,sitetitle,1 as MemberPerssion from familyProfile  inner  join member on member.familyId=familyprofile.FamilyId where userid=@Userid and approval=1 and auditing=0  and familyprofile.FamilyId=@familyId)
return 
end if(exists((SELECT  FamilyProfile.domainName, FamilyProfile.FamilyId, FamilyProfile.creator, FamilyProfile.creatorId, FamilyProfile.siteTitle, 2 AS MemberPerssion
   FROM         Member INNER JOIN
                         FriendlyFamily ON Member.FamilyId = FriendlyFamily.FamilyId INNER JOIN
                         FamilyProfile ON FriendlyFamily.FriendlyFamilyId = FamilyProfile.FamilyId
   WHERE     (Member.UserId = @userid) AND (Member.Approval = 1) and (FriendlyFamily.Approval = 1)  and familyprofile.FamilyId=@familyId)))
begin
/*判断它在友好家庭中的成员权限*/
   SELECT     FamilyProfile.domainName, FamilyProfile.FamilyId, FamilyProfile.creator, FamilyProfile.creatorId, FamilyProfile.siteTitle, 2 AS MemberPerssion
   FROM         Member INNER JOIN
                         FriendlyFamily ON Member.FamilyId = FriendlyFamily.FamilyId INNER JOIN
                         FamilyProfile ON FriendlyFamily.FriendlyFamilyId = FamilyProfile.FamilyId
   WHERE     (Member.UserId = @userid) AND (Member.Approval = 1) and (FriendlyFamily.Approval = 1)  and familyprofile.FamilyId=@familyId
   return
end/*判断它是否在友好家庭中的是户主的权限*/
if(exists(SELECT      FamilyProfile.domainName,FamilyProfile.FamilyId, FamilyProfile.creator, FamilyProfile.creatorId,FamilyProfile.siteTitle, 2 AS MemberPerssion 
   FROM         FamilyProfile INNER JOIN
                         FriendlyFamily ON FriendlyFamily.FamilyId = FamilyProfile.FamilyId INNER JOIN
                         FamilyProfile AS FamilyProfile_1 ON FriendlyFamily.FriendlyFamilyId = FamilyProfile_1.FamilyId
   WHERE     (FriendlyFamily.Approval = 1) AND (FamilyProfile_1.creatorId = @userId) and familyprofile.FamilyId=@familyId))
begin
   SELECT      FamilyProfile.domainName,FamilyProfile.FamilyId, FamilyProfile.creator, FamilyProfile.creatorId,FamilyProfile.siteTitle, 2 AS MemberPerssion 
   FROM         FamilyProfile INNER JOIN
                         FriendlyFamily ON FriendlyFamily.FamilyId = FamilyProfile.FamilyId INNER JOIN
                         FamilyProfile AS FamilyProfile_1 ON FriendlyFamily.FriendlyFamilyId = FamilyProfile_1.FamilyId
   WHERE     (FriendlyFamily.Approval = 1) AND (FamilyProfile_1.creatorId = @userId) and familyprofile.FamilyId=@familyId
end
   RETURN

Answer 1

谢谢,但是你看这样的代码.
判断有没有纪录.然后返回纪录,好笨的代码啊
 if(exists(select domainName,familyId,creator,creatorid,sitetitle,0 as MemberPermission from familyProfile where creatorid=@Userid and familyId=@familyId))
 begin 
   select domainName,familyId,creator,creatorid,sitetitle,0 as MemberPermission from familyProfile where creatorid=@Userid and familyId=@familyId
   return 
 end

Answer 2

if(exists(select 1 from familyProfile where creatorid=@Userid and familyId=@familyId))
 begin 
   select domainName,familyId,creator,creatorid,sitetitle,0 as MemberPermission from familyProfile where creatorid=@Userid and familyId=@familyId
   return 
 end

Answer 3

if(exists((SELECT 1 FROM Member 
INNER JOIN FriendlyFamily ON Member.FamilyId = FriendlyFamily.FamilyId 
INNER JOIN FamilyProfile ON FriendlyFamily.FriendlyFamilyId =FamilyProfile.FamilyId
   WHERE Member.UserId = @userid and familyprofile.FamilyId=@familyId 
AND Member.Approval = 1 and FriendlyFamily.Approval = 1  ))