Using UNION on results not on a SELECT statament

• danzap

  SSC Rookie

  Points: 28

  More actions

  January 20, 2009 at 10:12 am

  #205558

  Hi everybody.

  I need to create a union result for the following case.

  I have 2 or more procedures returning the same data fields with diferent conditions, calculations etc.

  How could I join, I mean, unite them (just like a UNION clause on a SELECT statement)

  Example:

  select fieldA, fieldB, fieldC from mytable where fieldA>10

  UNION

  select fieldA, fieldB, fieldC from temptable where Fielddatetime > last_week_date

  I need something like this

  exec procedure1

  UNION

  exec procedure2

  I cannot use a SELECT UNION because there many calculations on the Stored Procedure code and at the end each proc will return a "1 row result set".

  How should I do this?

  Is it the right way or there is another simple solution?

  Thanks

• Roy Ernest

  SSC-Dedicated

  Points: 38780

  More actions

  January 20, 2009 at 10:27 am

  #929806

  You can create a temp table or a variable table and insert into the temp table the values you get from your SP's

  DECLARE @TempTab TABLE

  ( lastName1VarChar(50),

  firstName1VarChar(50),

  address1VarChar(255))

  insert into @TempTab

  (lastName1,firstName1,address1)

  exec procedure1

  insert into @TempTab

  (lastName1,firstName1,address1)

  exec procedure2

  Select * from @TempTab

  -Roy

• danzap

  SSC Rookie

  Points: 28

  More actions

  January 20, 2009 at 10:33 am

  #929808

  It´s really simple.

  And the best part is that we can order contents of the TempTable.

  Thanks a lot.

• Roy Ernest

  SSC-Dedicated

  Points: 38780

  More actions

  January 20, 2009 at 10:43 am

  #929818

  Glad to be of help.

  -Roy