USP_GETMERCHANTACCOUNTIDFORPAGE

CREATE procedure dbo.USP_GETMERCHANTACCOUNTIDFORPAGE(@PageId int)
as
begin
   declare @tblPageParts table(Id int,PartId int,Type int)

   insert into @tblPageParts
   select P.ID,P.SiteContentID,CT.ID from PageContent P
   inner join SiteContent S on P.SiteContentID=S.ID
   inner join ContentTypes CT ON S.ContentTypesID=CT.ID
   where P.SitePagesID=@PageId and CT.ID in(45,47,114,154,118,9102,9111,92,117,9101,9103,112)

     --45 Donation

     --47,154 Event & Event 2

     --114 Ecards

     --118 Transaction manager

     --9111 ADF

     --9102 Payment 2.0

     --92 PPM

     --117 Membership BBEC

     --9101 Sponsorship Status

     --9103 Sponsorship Form

     --112 Payment 1.0


   ---Template Logic Begins


        declare @TemplatePageId int=0

        select @TemplatePageId=TemplatePageId from SitePages where ID=@PageId

        if isnull(@TemplatePageId,0)>0
        begin
         insert into @tblPageParts
         select P.ID,P.SiteContentID,CT.ID from PageContent P
         inner join SiteContent S on P.SiteContentID=S.ID
         inner join ContentTypes CT ON S.ContentTypesID=CT.ID
         where P.SitePagesID=@TemplatePageId and 
         CT.ID in(45,47,114,154,118,9102,9111,92,117,9101,9103,112)
        end

     -----Template Logic Ends


   if(exists(select count(*) from @tblPageParts))
   begin

   declare @tblMerchants table(MerchantAccount int)

   declare @max int,@counter int=1

   select @max=count(*) from @tblPageParts

   while @counter<=@max
   begin

          declare @partId int,@type int, @id int

                select @partId=PartId,@id=Id,@type=Type from
                (
                    select *,ROW_NUMBER() OVER (ORDER BY (select null)) as RowNo from @tblPageParts
                ) as T
                where RowNo=@counter

                    insert into @tblMerchants
                    exec dbo.USP_GETMERCHANTACCOUNTIDFORPART @partId,@type

                set @counter=@counter+1;

   end

   select distinct TM.MerchantAccount from @tblMerchants TM

   end

end;