Results 1 to 4 of 4
  1. #1
    Join Date
    Mar 2007
    Posts
    97

    Unanswered: Strange behavior in dynamic sql

    Hi folks,

    I have some code, that just works. But when I put it into a exec() I get a strange error. First the code
    Code:
    exec ('
    select 
    year,quarter, min(price) as minimum 
    into #temptable from 
    (
       select 
          ntile(4) over (partition by year,quarter order by price) as rang
          ,year
          ,quarter
          ,price
       from
       (
          select distinct id,year,quarter,price from #tbl1
       ) as a
    ) as b 
    group by rang,year,quarter
    
    Select year ,quarter,
    (
       SELECT CAST(minimum as varchar(max)) + "," 
       FROM #temptable t2 
       where t1.year=t2.year AND t1.quarter=t2.quarter
       FOR XML PATH("") 
    )
    from #temptable t1 
    group by year,quarter
    
    ')
    SQL Server says, that Insert Into is missing a column name. It points at line with FOR XML PATH("").
    Any Idea what's wrong here?

    The Output without exec (and correct quotes) looks like:
    Code:
    2004	3	0.00,252.90,331.40,470.00,
    2004	4	0.00,241.00,325.00,450.00,
    2005	1	102.00,242.90,326.37,448.00,
    2005	2	0.00,253.00,340.00,480.00,
    2005	3	0.00,250.00,325.00,465.00,
    2005	4	43.00,260.00,355.00,490.00,
    Thank you

  2. #2
    Join Date
    Mar 2007
    Posts
    97
    OK,
    Code:
    SET QUOTED_IDENTIFIER OFF
    as first statement did it :-)

  3. #3
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Why are you using dynamic sql?
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  4. #4
    Join Date
    Mar 2007
    Posts
    97
    In this case ... none. You don't see the function here, just their output.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •