Stored Procedure not working with insert into temp table

Question

I have a Stored Procedure and cte not playing nice together. I have tried doing some research and have been unsuccessful. When I comment out insert into the ##tab on the cte the SP works and I see the results of the cte. it's just when I'm inserting into a Global temp table ##tab.

I'm receiving these errors. Msg 102, Level 15, State 1, Line 39

Incorrect syntax near 'ALL'.

Msg 102, Level 15, State 1, Line 39

Incorrect syntax near 'ALL'.

Msg 102, Level 15, State 1, Line 39

Incorrect syntax near 'ALL'.

Msg 102, Level 15, State 1, Line 39

Incorrect syntax near 'ALL'.

Msg 102, Level 15, State 1, Line 39

Incorrect syntax near 'ALL'.

    SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO




ALTER PROCEDURE [dbo].[csp_CALL_UCBI_DCN_DATES]
    @FMDATE DATETIME ='2014-01-20 00:00:00.000', 
    @TODATE DATETIME ='2014-06-30 00:00:00.000'
AS
BEGIN
SET NOCOUNT ON;

DECLARE 
--@FMDATE DATETIME
--,@TODATE DATETIME
@FM_DATEPART_MONTH VARCHAR(2)
,@FM_DATEPART_YEAR VARCHAR(4)
,@TO_DATEPART_MONTH VARCHAR(2)
,@TO_DATEPART_YEAR VARCHAR(4)

--SET @FMDATE = '2014-01-20 00:00:00.000'
--SET @TODATE = '2014-06-30 00:00:00.000'
SET @FM_DATEPART_MONTH = CAST(DATEPART(M,@FMDATE) AS VARCHAR(2))
SET @FM_DATEPART_YEAR = CAST(DATEPART(YYYY,@FMDATE) AS VARCHAR(4))
SET @TO_DATEPART_MONTH = CAST(DATEPART(M,@TODATE) AS VARCHAR(2))
SET @TO_DATEPART_YEAR = CAST(DATEPART(YYYY,@TODATE) AS VARCHAR(4))

  CREATE TABLE ##tab (id INT IDENTITY(1,1) PRIMARY KEY,myDate VARCHAR(50), SQLname VARCHAR(50),myMonth VARCHAR(50),myYear VARCHAR(50))
;WITH cte(myDate,myMonth,myYear,SQLname) AS (

    SELECT @FMDATE  AS myDate
    ,CAST(DATEPART(M,@FMDATE) AS VARCHAR(2)) AS myMonth
    ,CAST(DATEPART(YYYY,@FMDATE) AS VARCHAR(4)) AS myYear
    ,CAST(DATEPART(M,@FMDATE) AS VARCHAR(2))+'_'+CAST(DATEPART(YYYY,@FMDATE) AS VARCHAR(4)) AS SQLname
    UNION ALL
    SELECT DATEADD(MONTH,1,myDate) AS myDate
    ,CAST(DATEPART(M,DATEADD(MONTH,1,myDate)) AS VARCHAR(2)) AS myMonth
    ,CAST(DATEPART(YYYY,DATEADD(MONTH,1,myDate)) AS VARCHAR(4)) AS myYear
    ,CAST(DATEPART(M,DATEADD(MONTH,1,myDate)) AS VARCHAR(2))+'_'+CAST(DATEPART(YYYY,DATEADD(MONTH,1,myDate)) AS VARCHAR(4)) AS SQLname
    FROM cte
    WHERE DATEADD(MONTH,1,myDate) <=  @TODATE
)
INSERT INTO [##tab]
(myDate,myMonth,myYear,SQLname)
SELECT *
FROM cte
--output INSERTED.myDate,INSERTED.myMonth,INSERTED.myYear,INSERTED.SQLname into [##tab]
--(myMonth,myYear,SQLname)
--OPTION (MAXRECURSION 0)

------------------------------------

DECLARE @sql VARCHAR(8000)
, @i INT
,@count INT
, @sqlname VARCHAR(15)
, @myMonth VARCHAR(50) 
, @myYear VARCHAR(50)

SET @i=1
SELECT @count= COUNT(*) FROM ##tab
WHILE @i <= @count

BEGIN
SELECT @SQLname=SQLname, @myMonth=myMonth, @myYear=myYear FROM ##tab WHERE id=@i 

SET @sql = '
  SELECT  
''UCBI_DCE_'+@SQLname+''' AS [Table],
CAST('''+@myMonth+'/'+@myYear+'/01'' AS DATETIME) AS Date,
        lEntity ,
        lValue ,
        lAccount ,
        lICP ,
        lCustom1 ,
        lCustom2 ,
        lCustom3 ,
        lCustom4 ,
        dP0_Input ,
        dP0_InputTransType ,
        dP1_Input ,
        dP1_InputTransType ,
        dP2_Input ,
        dP2_InputTransType ,
        dP3_Input ,
        dP3_InputTransType ,
        dP4_Input ,
        dP4_InputTransType ,
        dP5_Input ,
        dP5_InputTransType ,
        dP6_Input ,
        dP6_InputTransType ,
        dP7_Input ,
        dP7_InputTransType ,
        dP8_Input ,
        dP8_InputTransType ,
        dP9_Input ,
        dP9_InputTransType ,
        dP10_Input ,
        dP10_InputTransType ,
        dP11_Input ,
        dP11_InputTransType ,
        dTimestamp 
        FROM [DB100].[DB].[dbo].[DCN_'+@SQLname+']
        UNION ALL
'
IF @i = @count SET @sql = LEFT(@sql,LEN(@sql) - 15)
--PRINT @sql
EXEC (@sql)

SET @i=@i+1
END
IF(OBJECT_ID('tempdb..##tab') IS NOT NULL)
BEGIN
    DROP TABLE ##tab
END
END

GO

Answer 1

I believe this is the problem. I get that you are trying to build some dynamic sql and UNION ALL. The problem is that you execute the @sql in the loop while the statement is still building. So basically you build and execute @sql every iteration. Move the final execute outside of the loop. I have shortened your code to show where the issue is.

Oh and the reason it was working when you comment out the insert into the temp table is because since there were no rows in that table you never get into the loop.

BEGIN
SELECT @SQLname=SQLname, @myMonth=myMonth, @myYear=myYear FROM ##tab WHERE id=@i 

SET @sql = '
  YOUR CODE 
  UNION ALL'
IF @i = @count SET @sql = LEFT(@sql,LEN(@sql) - 15)
--PRINT @sql

/*HERE YOU EXECUTE WHILE IN LOOP SO THE LAST LINE IS UNION ALL*/
EXEC (@sql)
SET @i=@i+1
END

Here's the fix

SET @i=1
/* Set @sql outside of loop */
SET @sql = ''
SELECT @count= COUNT(*) FROM ##tab
WHILE @i <= @count

BEGIN
SELECT @SQLname=SQLname, @myMonth=myMonth, @myYear=myYear FROM ##tab WHERE id=@i 

/* Modified this to add to @sql each interation to build your select statement.*/
SET @sql = @sql + '
  select 1 
  UNION ALL'

IF @i = @count SET @sql = LEFT(@sql,LEN(@sql) - 15)

SET @i=@i+1
END

/* Moved exec out of loop*/
EXEC (@sql)

Answer 2

Why is the union ALL at the bottom of your dynamic SQL query? It's where the error comes from...union all combines two seperate select statements with like fields

simple example:

 select 1 
 union all
 select 2

this will return 2 rows...1 and 2.

Here you have it here with nothing following it. Remove the union all, unless you have another statement to run under it, in which case...include the other statement in your code.