title: Stored procedure & union
body: <p>I'm creating a stored procedure for data validation that is pretty straight forward.  I have several sql statements that i wish to run and simply display the results to compare to the source.  I combined the sql statements with a union but you can not union together columns of different data types.  Hopefully someone will have a suggestion on a better way to proceed?   </p>
  
  <pre><code>USE [employee_data]
  GO
  
  SET ANSI_NULLS ON
  GO
  SET QUOTED_IDENTIFIER ON
  GO
  ALTER PROCEDURE [dbo].[data_employee]
  
  AS
  BEGIN
  
  declare @LtYear as varchar(10)
  set @LtYear = '2012'
  
  SELECT 'Sum of total salary for ' + @LtYear as title, sum([Total_Salary]) as Salary
  From [employee_data].[dbo].[employee]
  where year = @LtYear
  
  UNION
  select 'Overall Average Salary ' + @LatestYear as title, avg([Total_Salary]) as     'Average Salary'
  From [employee_data].[dbo].[employee]
  where year = @LtYear
  
  SELECT 'Employee Name - 1' as title, first_name+' '+last_name 
  From [employee_data].[dbo].[employee]
  where year = 2012 --@LtYear
  and [First_Name] = 'first'
  and [Last_Name] = 'last'
  
  UNION
  SELECT 'Individual Employee Experience ' + @LtYear as title, cast([work_Exp]as varchar) as 'Work Experience'
  From [employee_data].[dbo].[employee]
  where year = @LtYear
  and [First_Name] = 'first'
  and [Last_Name] = 'last'
  </code></pre>
  
  <p>I would like to see the results as follows:</p>
  
  <p>Sum of Total Salary: 50000000<br>
  Overall Average Salary: 37000<br>
  Employee Name - 1: First Last<br>
  Individual Employee Experience 2012: 12  </p>