Incorrect syntax near the keyword 'UNION'

hmmm...

I tried this also with same errors
ALTER PROCEDURE [dbo].[SSRS_Get_RevenueByMonth]

@NumberOfMonthsBack      int

AS

-- Grab current month data
IF OBJECT_ID('#CurrentMonthCollections_IL_FeeBased_Holder') IS NOT NULL
BEGIN
      DROP TABLE #CurrentMonthCollections_IL_FeeBased_Holder
END
      CREATE TABLE #CurrentMonthCollections_IL_FeeBased_Holder
      (projfee            money,
      projgross            money,
      dailyrunrate      money,
      var1                  money,
      inhouse1            money,
      inhouse2            money,
      grossgoal            money,
      groupname            varchar(15),
      postedamount      money,
      budget                  money)


-- Now, generate the data for IL bars and insert into temp table
INSERT INTO #CurrentMonthCollections_IL_FeeBased_Holder
EXEC SSRS_Get_CurrentMonthCollections_AZ_FeeBased

UNION

-- Grab past month data

select * from
(
SELECT      Budget,
            InHouse,
            ProjFee,
            MonthName,
            YearValue

FROM Dashboard_RevenueByMonth
) as q
ORDER BY CASE MonthName WHEN  'January' THEN 1
                                    WHEN 'February' THEN 2
                                    WHEN 'March' THEN 3
                                    WHEN 'April' THEN 4
                                    WHEN 'May' THEN 5
                                    WHEN 'June' THEN 6
                                    WHEN 'July' THEN 7
                                    WHEN 'August' THEN 8
                                    WHEN 'September' THEN 9
                                    WHEN 'October' THEN 10
                                    WHEN 'November' THEN 11
                                    WHEN 'December' THEN 12
                                    ELSE 99 END ASC, YearValue

END

I've tried to convert my stored proc to a function, but it is a huge stored proc, and I get all sorts of errors when trying.

that is NOT what I suggested...

ALTER PROCEDURE [dbo].[SSRS_Get_RevenueByMonth]

@NumberOfMonthsBack     int

AS

-- Grab current month data
IF OBJECT_ID('#CurrentMonthCollections_IL_FeeBased_Holder') IS NOT NULL
BEGIN
     DROP TABLE #CurrentMonthCollections_IL_FeeBased_Holder
END
     CREATE TABLE #CurrentMonthCollections_IL_FeeBased_Holder
     (projfee          money,
     projgross          money,
     dailyrunrate     money,
     var1               money,
     inhouse1          money,
     inhouse2          money,
     grossgoal          money,
     groupname          varchar(15),
     postedamount     money,
     budget               money)


-- Now, generate the data for IL bars and insert into temp table
INSERT INTO #CurrentMonthCollections_IL_FeeBased_Holder
EXEC SSRS_Get_CurrentMonthCollections_AZ_FeeBased

SELECT * FROM #CurrentMonthCollections_IL_FeeBased_Holder
UNION

-- Grab past month data

select * from
(
SELECT     Budget,
          InHouse,
          ProjFee,
          MonthName,
          YearValue

FROM Dashboard_RevenueByMonth
) as q
ORDER BY CASE MonthName WHEN  'January' THEN 1
                              WHEN 'February' THEN 2
                              WHEN 'March' THEN 3
                              WHEN 'April' THEN 4
                              WHEN 'May' THEN 5
                              WHEN 'June' THEN 6
                              WHEN 'July' THEN 7
                              WHEN 'August' THEN 8
                              WHEN 'September' THEN 9
                              WHEN 'October' THEN 10
                              WHEN 'November' THEN 11
                              WHEN 'December' THEN 12
                              ELSE 99 END ASC, YearValue

END

I'd prefer not to turn it into a function (due to a pain in errors I was getting when trying to convert it)..I've done it this way in the past

If I just run this part:

IF OBJECT_ID('#CurrentMonthCollections_IL_FeeBased_Holder') IS NOT NULL
BEGIN
     DROP TABLE #CurrentMonthCollections_IL_FeeBased_Holder
END
     CREATE TABLE #CurrentMonthCollections_IL_FeeBased_Holder
     (projfee          money,
     projgross          money,
     dailyrunrate     money,
     var1               money,
     inhouse1          money,
     inhouse2          money,
     grossgoal          money,
     groupname          varchar(15),
     postedamount     money,
     budget               money)


-- Now, generate the data for IL bars and insert into temp table
INSERT INTO #CurrentMonthCollections_IL_FeeBased_Holder
EXEC SSRS_Get_CurrentMonthCollections_AZ_FeeBased


I get this error:

Msg 8164, Level 16, State 1, Procedure SSRS_Get_CurrentMonthCollections_AZ_FeeBased, Line 490
An INSERT EXEC statement cannot be nested.

OK, can we do 2 things

1) Get it working using temp tables

2) Help me convert my stored proc to a function and try it that way...I got errors when converting.

the called stored proc is also inserting data into some temp tables so that is probably why I got the nested insert error

I'm gonna try agian to convert my called stored proc to a UDF

dba123,
> An INSERT EXEC statement cannot be nested.

Yes, this is a restriction with INSERT-EXEC. See this article for alternative solutions:
http://www.sommarskog.se/share_data.html

well, the called stored procedure is currently insterting into a temp table.  I could change it to insert into a regular table..then in this stored proc, just do a select on the regular table to get around all this.  Then, each time the called stored proc is called, delete the data in the regular table to start over each time it's called.