Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 787
  • Last Modified:

Incorrect syntax near the keyword 'UNION'

I am not sure why this is malformed:

Msg 156, Level 15, State 1, Procedure SSRS_Get_RevenueByMonth, Line 13
Incorrect syntax near the keyword 'UNION'.
Msg 102, Level 15, State 1, Procedure SSRS_Get_RevenueByMonth, Line 42
Incorrect syntax near 'END'.


ALTER PROCEDURE [dbo].[SSRS_Get_RevenueByMonth]

@NumberOfMonthsBack      int

AS

-- Grab current month data

EXEC storedprocname

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
0
dba123
Asked:
dba123
  • 7
  • 2
1 Solution
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
you cannot do a UNION of a procedure and a select.

you could convert that procedure into a table-valued function, and change your proc into:

ALTER PROCEDURE [dbo].[SSRS_Get_RevenueByMonth]

@NumberOfMonthsBack     int

AS

-- Grab current month data

SELECT * FROM dbo.storedfunctionname()
UNION ALL

-- 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
0
 
dba123Author Commented:
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
0
 
dba123Author Commented:
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.
0
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
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

0
 
dba123Author Commented:
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
0
 
dba123Author Commented:
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.
0
 
dba123Author Commented:
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
0
 
dba123Author Commented:
I'm gonna try agian to convert my called stored proc to a UDF
0
 
Aneesh RetnakaranDatabase AdministratorCommented:
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
0
 
dba123Author Commented:
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.
0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

  • 7
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now