how to combine between this store procedure RSS

• • Reply
  

  Junior1012

  Member

  76 Points

  178 Posts

  how to combine between this store procedure

  Apr 21, 2012 01:39 PM|LINK

  Hi,

  I have a store procedure and its below

  ALTER PROC [dbo].[Event_Report] 
  
  AS
  BEGIN

  
  (select p.NominatorOrganisation as Event_Att_Name from POAForm p
  union
  select i.Name from InKindSponsorshipForm i
  union
  select n.BeneficiarySponsorName from NominationDetailsForm n)
  (select p.NominatorDesignation as Event_Att_Designation from POAForm p
  union
  select i.Designation from InKindSponsorshipForm i
  union
  select n.Designation from NominationDetailsForm n)
  (select p.ContactNoOne as Event_Att_OfficeTel from POAForm p
  union
  select i.ContactNoOne from InKindSponsorshipForm i
  union
  select n.ContactNoOne from NominationDetailsForm n)
  (select p.ContactNoTwo as Event_Att_Mobile from POAForm p
  union
  select i.ContactNoTwo from InKindSponsorshipForm i
  union
  select n.ContactNoTwo from NominationDetailsForm n)
  (select p.NominatorEmail as Event_Att_Email from POAForm p
  union
  select i.Email from InKindSponsorshipForm i
  union
  select n.Email from NominationDetailsForm n)

  select e.EventName,e.VenueName,e.EventTime,e.GuestOfHonoursName,
  e.GuestOfHonoursDesignation, e.EventBrochureOrInvitation,e.LocationMap,
  e.Capacity,e.URLMapLink, tp.ThirdPartySaluation,tp.ThirdPartyName, tp.ThirdPartyOrganization, 
  tp.ThirdPartyDesignation, tp.ThirdPartyOfficeTel,tp.ThirdPartyMobileNo,tp.ThirdPartyEmail
  from Event e INNER JOIN Link l on e.EventID = l.EventID join ThirdPartyEventInvitation tp
  on l.ThirdPartyEventInvID = tp.ThirdPartyEventInvID join POAForm p on tp.ThirdPartyEventInvID =
  p.ThirdPartyEventInvID join InKindSponsorshipForm i on p.POAFormID = i.POAFormID join 
  NominationDetailsForm n on p.POAFormID = n.POAFormID 
  where e.DeleteStatus = 'N' and p.DeleteStatus = 'N' and i.DeleteStatus = 'N' and n.DeleteStatus='N'
  and tp.DeleteStatus='N'

  
  END

  The problem is how to combine between these select set because i cant use union or union all.

  eg. I would like to combine

  (select p.NominatorOrganisation as Event_Att_Name from POAForm p
  union
  select i.Name from InKindSponsorshipForm i
  union
  select n.BeneficiarySponsorName from NominationDetailsForm n)

  ????.......what word shoul i use between them to connect???????

  (select p.NominatorDesignation as Event_Att_Designation from POAForm p
  union
  select i.Designation from InKindSponsorshipForm i
  union
  select n.Designation from NominationDetailsForm n)

  ????.......what word shoul i use between them to connect???????

  select e.EventName,e.VenueName,e.EventTime,e.GuestOfHonoursName,
  e.GuestOfHonoursDesignation, e.EventBrochureOrInvitation,e.LocationMap,
  e.Capacity,e.URLMapLink, tp.ThirdPartySaluation,tp.ThirdPartyName, tp.ThirdPartyOrganization, 
  tp.ThirdPartyDesignation, tp.ThirdPartyOfficeTel,tp.ThirdPartyMobileNo,tp.ThirdPartyEmail
  from Event e INNER JOIN Link l on e.EventID = l.EventID join ThirdPartyEventInvitation tp
  on l.ThirdPartyEventInvID = tp.ThirdPartyEventInvID join POAForm p on tp.ThirdPartyEventInvID =
  p.ThirdPartyEventInvID join InKindSponsorshipForm i on p.POAFormID = i.POAFormID join 
  NominationDetailsForm n on p.POAFormID = n.POAFormID 
  where e.DeleteStatus = 'N' and p.DeleteStatus = 'N' and i.DeleteStatus = 'N' and n.DeleteStatus='N'
  and tp.DeleteStatus='N'

  Please, can anyone give me idea for this porblem. Thanks in advance.

  • Moved by Song-Tian - MSFT on Apr 24, 2012 02:42 AM
• • Reply
  

  jigarbjpatel

  Member

  454 Points

  88 Posts

  Re: how to combine between this store procedure

  Apr 24, 2012 03:25 AM|LINK

  For the first 2 queries (for name and designation), you can do something like this:

  SELECT p.NominatorOrganisation as Event_Att_Name,p.NominatorDesignation as Event_Att_Designation   from POAForm p
  UNION
  SELECT i.Name,i.Designation   from InKindSponsorshipForm i
  UNION
  SELECT n.BeneficiarySponsorName ,n.Designation   from InKindSponsorshipForm n

  You can then extend it similarly for ContactOne,ContactTwo and Email fields.

• • Reply
  

  Junior1012

  Member

  76 Points

  178 Posts

  Re: how to combine between this store procedure

  Apr 24, 2012 05:53 AM|LINK

  Hi, 

  Thanks for give me idea. I got the answer on other way arond and its like below :

  ALTER PROC [dbo].[Event_Report]
  
  AS
  BEGIN
  select * from
  (select p.NominatorOrganisation as Event_Att_Name from POAForm p
  union
  select i.Name from InKindSponsorshipForm i
  union
  select n.BeneficiarySponsorName from NominationDetailsForm n)as A
  CROSS JOIN
  (select p.NominatorDesignation as Event_Att_Designation from POAForm p
  union
  select i.Designation from InKindSponsorshipForm i
  union
  select n.Designation from NominationDetailsForm n)AS B
  CROSS JOIN
  (select p.ContactNoOne as Event_Att_OfficeTel from POAForm p
  union
  select i.ContactNoOne from InKindSponsorshipForm i
  union
  select n.ContactNoOne from NominationDetailsForm n)AS C
  CROSS JOIN
  (select p.ContactNoTwo as Event_Att_Mobile from POAForm p
  union
  select i.ContactNoTwo from InKindSponsorshipForm i
  union
  select n.ContactNoTwo from NominationDetailsForm n)AS D
  CROSS JOIN
  (select p.NominatorEmail as Event_Att_Email from POAForm p
  union
  select i.Email from InKindSponsorshipForm i
  union
  select n.Email from NominationDetailsForm n)AS E
  CROSS JOIN

  (select e.EventName,e.VenueName,e.EventTime,e.GuestOfHonoursName,
  e.GuestOfHonoursDesignation, e.EventBrochureOrInvitation,e.LocationMap,
  e.Capacity,e.URLMapLink, tp.ThirdPartySaluation,tp.ThirdPartyName, tp.ThirdPartyOrganization,
  tp.ThirdPartyDesignation, tp.ThirdPartyOfficeTel,tp.ThirdPartyMobileNo,tp.ThirdPartyEmail
  from Event e INNER JOIN Link l on e.EventID = l.EventID join ThirdPartyEventInvitation tp
  on l.ThirdPartyEventInvID = tp.ThirdPartyEventInvID join POAForm p on tp.ThirdPartyEventInvID =
  p.ThirdPartyEventInvID join InKindSponsorshipForm i on p.POAFormID = i.POAFormID join
  NominationDetailsForm n on p.POAFormID = n.POAFormID
  where e.DeleteStatus = 'N' and p.DeleteStatus = 'N' and i.DeleteStatus = 'N' and n.DeleteStatus='N'
  and tp.DeleteStatus='N') AS F
  END

  • Edited by Junior1012 on Apr 24, 2012 05:53 AM
  • Edited by Junior1012 on Apr 24, 2012 05:58 AM
  • Marked as answer by Junior1012 on Apr 24, 2012 05:58 AM