none
Dynamic Pivot Table - Incorrect syntax near '+ @cols +'

    Question

  • Dear sir:

    I made the following store procedure:

    CREATE PROCEDURE spGetOpenOrdersLaborRequireByDate

       

    AS

    BEGIN

        Declare @RowsAdded int

       

        Declare @tbShipDate table (ShipDate date)

        insert @tbShipDate

        Select Distinct ShipDate From fGetOpenOrdersLaborRequireByDate () order by ShipDate

        Set @RowsAdded =@@ROWCOUNT

       

        declare @Date date

        Declare @cols varchar(2000)

        declare cDate cursor scroll for select ShipDate from @tbShipDate

        If @RowsAdded >0

           Begin

               Fetch first from cDate into @Date

               Set @cols ='['+ convert(varchar,@Date)+']'

               Fetch absolute 2 from cDate into @Date

               While @@FETCH_STATUS=0

                  Begin

                      Set @cols =@cols +',[' + convert(varchar,@Date)+']'

                      Fetch next from cDate into @Date

                  End

           End

        Select * from fGetOpenOrdersLaborRequireByDate ()  a pivot (sum(ELabor) for ShipDate in (' + @cols + ')) b

    END

    GO

    When I parse it, it shows "Msg 102, Level 15, State 1, Procedure spGetOpenOrdersLaborRequireByDate, Line 27
    Incorrect syntax near ' + @cols + '

    Would you please tell me how to solve this problem?

    Regards,

    Shell


    Shell

    Wednesday, August 20, 2014 11:25 AM

Answers

  • EXEC('Select * from fGetOpenOrdersLaborRequireByDate ()  a pivot (sum(ELabor) for ShipDate in (' + @cols + ')) b')

    you need to use dynamic sql

    Please Mark This As Answer if it helps to solve the issue Visakh ---------------------------- http://visakhm.blogspot.com/ https://www.facebook.com/VmBlogs

    Wednesday, August 20, 2014 11:49 AM
  • You have to use dynamic SQL for the in operation:

    CREATE PROCEDURE Spgetopenorderslaborrequirebydate 
    AS 
      BEGIN 
          DECLARE @RowsAdded INT 
          DECLARE @tbShipDate TABLE 
            ( 
               shipdate DATE 
            ) 
    
          INSERT @tbShipDate 
          SELECT DISTINCT shipdate 
          FROM   Fgetopenorderslaborrequirebydate () 
          ORDER  BY shipdate 
    
          SET @RowsAdded =@@ROWCOUNT 
    
          DECLARE @Date DATE 
          DECLARE @cols VARCHAR(2000) 
          DECLARE cdate CURSOR scroll FOR 
            SELECT shipdate 
            FROM   @tbShipDate 
    
          IF @RowsAdded > 0 
            BEGIN 
                FETCH first FROM cdate INTO @Date 
    
                SET @cols ='[' + CONVERT(VARCHAR, @Date) + ']' 
    
                FETCH absolute 2 FROM cdate INTO @Date 
    
                WHILE @@FETCH_STATUS = 0 
                  BEGIN 
                      SET @cols =@cols + ',[' + CONVERT(VARCHAR, @Date) + ']' 
    
                      FETCH next FROM cdate INTO @Date 
                  END 
            END 
    
          EXEC ( 
    'Select *  from fGetOpenOrdersLaborRequireByDate ()  a pivot (sum(ELabor) for ShipDate in (''' 
        + @cols + ''')) b') 
    END   
    GO
    

    Dynamic SQL:

    http://www.sqlusa.com/bestpractices/dynamicsql/


    Kalman Toth Database & OLAP Architect SQL Server 2014 Design & Programming
    New Book / Kindle: Exam 70-461 Bootcamp: Querying Microsoft SQL Server 2012








    Wednesday, August 20, 2014 1:10 PM

All replies

  • EXEC('Select * from fGetOpenOrdersLaborRequireByDate ()  a pivot (sum(ELabor) for ShipDate in (' + @cols + ')) b')

    you need to use dynamic sql

    Please Mark This As Answer if it helps to solve the issue Visakh ---------------------------- http://visakhm.blogspot.com/ https://www.facebook.com/VmBlogs

    Wednesday, August 20, 2014 11:49 AM
  • You have to use dynamic SQL for the in operation:

    CREATE PROCEDURE Spgetopenorderslaborrequirebydate 
    AS 
      BEGIN 
          DECLARE @RowsAdded INT 
          DECLARE @tbShipDate TABLE 
            ( 
               shipdate DATE 
            ) 
    
          INSERT @tbShipDate 
          SELECT DISTINCT shipdate 
          FROM   Fgetopenorderslaborrequirebydate () 
          ORDER  BY shipdate 
    
          SET @RowsAdded =@@ROWCOUNT 
    
          DECLARE @Date DATE 
          DECLARE @cols VARCHAR(2000) 
          DECLARE cdate CURSOR scroll FOR 
            SELECT shipdate 
            FROM   @tbShipDate 
    
          IF @RowsAdded > 0 
            BEGIN 
                FETCH first FROM cdate INTO @Date 
    
                SET @cols ='[' + CONVERT(VARCHAR, @Date) + ']' 
    
                FETCH absolute 2 FROM cdate INTO @Date 
    
                WHILE @@FETCH_STATUS = 0 
                  BEGIN 
                      SET @cols =@cols + ',[' + CONVERT(VARCHAR, @Date) + ']' 
    
                      FETCH next FROM cdate INTO @Date 
                  END 
            END 
    
          EXEC ( 
    'Select *  from fGetOpenOrdersLaborRequireByDate ()  a pivot (sum(ELabor) for ShipDate in (''' 
        + @cols + ''')) b') 
    END   
    GO
    

    Dynamic SQL:

    http://www.sqlusa.com/bestpractices/dynamicsql/


    Kalman Toth Database & OLAP Architect SQL Server 2014 Design & Programming
    New Book / Kindle: Exam 70-461 Bootcamp: Querying Microsoft SQL Server 2012








    Wednesday, August 20, 2014 1:10 PM