Union Stored Procedure

• mbrady5

  SSCrazy

  Points: 2835

  More actions

  September 29, 2015 at 11:25 am

  #311923

  Looking for some help to union stored procedure. The only thing that would be different is 1 variable.

  Exec MyStoredProc 1,?,?

  Union

  Exec My StoredProc 2,?,?

  Thanks

• drew.allen

  SSC Guru

  Points: 76949

  More actions

  September 29, 2015 at 1:26 pm

  #1830266

  UNION applies to sets and stored procedures are not sets, although they may return sets. If you want to union the results of two stored procedure calls, you'll need to store the results in separate tables and then union the tables. If you want UNION ALL, then you only need one table.

  DECLARE @Table1 TABLE AS (<table definition>);

  DECLARE @Table2 TABLE AS (<table definition>);

  INSERT INTO @Table1(<column list>)

  Exec MyStoredProc 1,?,?

  INSERT INTO @Table2(<column list>)

  Exec My StoredProc 2,?,?

  SELECT <column list>

  FROM @Table1

  UNION

  SELECT <column list>

  FROM @Table2

  Drew

  J. Drew Allen
  Business Intelligence Analyst
  Philadelphia, PA

• Alan Burstein

  SSC Guru

  Points: 61136

  More actions

  September 29, 2015 at 8:05 pm

  #1830313

  What Drew said.

  I would add that, based on what you're showing UNION ALL is probably what you want. If you read Itzik Ben-Gan, he often refers to UNION as "UNION Distinct". What you need to understand is that UNION removes duplicates which usually involves a sort operator or a less optimal join algorithm in your query plan. Either way, I have never seen a situation where UNION was a faster way to go.

  My rule of thumb is: if you can't explain exactly why you are using UNION over UNION ALL then you need to use UNION ALL.

  "I cant stress enough the importance of switching from a sequential files mindset to set-based thinking. After you make the switch, you can spend your time tuning and optimizing your queries instead of maintaining lengthy, poor-performing code."

  -- Itzik Ben-Gan 2001