I have a stored procedure which works great. I want to make the same identical query but against a different SQL database.
Can I do a UNION ALL? If so, how?
Create Procedure HCGBATCH
AS
DECLARE @TotalInvoices int
DECLARE @InvoiceDollars decimal(10,2)
DECLARE @TaxAmt decimal(10,2)
DECLARE @RevDollars decimal(10,2)
DECLARE @FeeDollars decimal(10,2)
BEGIN
SET NOCOUNT ON;
set @TotalInvoices =
(select(COUNT(DISTINCT SOP10200.SOPNUMBE)) from SOP10200
where SOPNUMBE like 'HCG%' and SOP10200.SOPTYPE='3')
set @InvoiceDollars =
(select (CAST(SUM(XTNDPRCE) as decimal (10,2))) from SOP10200
where SOPNUMBE like 'HCG%' and SOP10200.SOPTYPE='3')
set @TaxAmt =
(select (CAST(SUM(TAXAMNT) as decimal (10,2))) from SOP10200
where SOPNUMBE like 'HCG%' and SOP10200.SOPTYPE='3')
set @RevDollars =
(select CAST(SUM(XTNDPRCE) as decimal (10,2)) from SOP10200
where SOPNUMBE like 'HCG%' and SOPTYPE='3' AND ITEMNMBR='REF-H-P')
set @FeeDollars =
(select CAST(SUM(XTNDPRCE) as decimal (10,2)) from SOP10200
where SOPNUMBE like 'HCG%' and SOPTYPE='3' AND ITEMNMBR='REF-H-FEE')
select
'USA' as Company,
@TotalInvoices as TotalInvoices,
@InvoiceDollars as InvoiceDollars,
@TaxAmt as Tax,
@RevDollars as RevenueDollars,
@FeeDollars as FeeDollars
END