CTE Help

• Prakash-205770

  Ten Centuries

  Points: 1079

  More actions

  September 18, 2007 at 1:56 am

  #197847

  Hi all,

  Will the CTE executes a Dynamic SQl.

  Please help me for the following.

  For Ex:

  With Cte as

  (

  Set @CntSQL = 'SELECT * FROM (SELECT A.Companyid,A.Category

  FROM ' + @Name + '.DBO.TABLE A join ' + @@Name + '.dbo.TAble B

  On A.Category = B.CategoryUsed) o PIVOT

  (COUNT(Category) FOR Category IN (

  ---

  ,[HT],[SAFETY],[SKILLS],[OTHER])) p'

  Exec (@CntSQL)

  )

  Thanks,

  Nithyapriya

  ---

  Kindest Regards,

  R

• SwePeso

  SSC-Dedicated

  Points: 39757

  More actions

  September 18, 2007 at 2:43 am

  #734234

  The real question is "Why do you think you need a CTE for this?".

  See http://www.sommarskog.se/dynamic_sql.html before you enter the land of denormalization.

   

   

  ---

  N 56°04'39.16"
  E 12°55'05.25"

• Piotr.Rodak

  SSCrazy Eights

  Points: 9376

  More actions

  September 18, 2007 at 2:52 am

  #734237

  BOL says that select expression in CTE has to meet same requirements as the one for view, so no dynamic SQL inside.

  ...and your only reply is slàinte mhath