hanging dynamic sql code

• craig 84462

  Say Hey Kid

  Points: 669

  More actions

  March 21, 2010 at 10:21 pm

  #218426

  HI there,

  Does anyone know why this code hangs and does not execute?

  Think theres a problem near the if @statisticvalue <-=100?

  thanks heaps

  *****************************************

  use Craig

  go

  Declare@DatabaseSysname

  Declare@SchemaNameSysname

  Declare@TableNameSysname

  Declare@ColumnNameSysname

  Declare@strExecnvarChar(Max)

  Declare@StatisticvalueBigInt

  -- Loop over all databases/tables/columns that are available.

  DeclarecsrDatabaseTableColumnstat Cursor For

  SELECT TOP 5 -- debug

  DatabaseName

  ,SchemaName

  ,TableName

  ,ColumnName

  ,Statisticvalue

  FROMdemo

  OpencsrDatabaseTableColumnstat

  FetchNext

  FromcsrDatabaseTableColumnstat

  Into @Database

  ,@SchemaName

  ,@TableName

  ,@ColumnName

  ,@Statisticvalue

  While@@FETCH_STATUS=0

  if @Statisticvalue <=100

  Begin

  Set@strExec=N'Insert'+' '+'demo2'+' '+'(Databasename,SchemaName,Tablename,

  Columnname,Statistic,Statisticsubpart,Statisticvalue,Snapshotdate)'

  Set@strExec=@strExec+'Select'+' '+''''+@Database+''''+','+

  +''''+@SchemaName+''''+','

  +''''+@TableName+''''+','

  +''''+@ColumnName+''''+','

  +'''CountDistinct'''+','

  +'Convert(varChar(100), '+@ColumnName+')'+','

  +'Count(0)'+','

  +'Getdate()'

  Set@strExec=@strExec+' '+'From'+' '+@Database+'.'+@SchemaName+'.'+@TableName

  +'group by' +' '+ 'Convert(varChar(100), '+@ColumnName+')'

  Exec@strExec

  FetchNext

  FromcsrDatabaseTableColumnstat

  Into @Database

  ,@SchemaName

  ,@TableName

  ,@ColumnName

  ,@Statisticvalue

  End

  ClosecsrDatabaseTableColumnstat

  DeallocatecsrDatabaseTableColumnstat

• Gayathri.Varadarajan

  SSCrazy

  Points: 2080

  More actions

  March 22, 2010 at 12:19 am

  #1137423

  craig 84462 (3/21/2010)

  ---

  HI there,

  While@@FETCH_STATUS=0

  if @Statisticvalue <=100

  Begin

  Try giving a begin after the while.

• craig 84462

  Say Hey Kid

  Points: 669

  More actions

  March 22, 2010 at 1:15 am

  #1137429

  thanks heaps for the reply, do you mean this?

  **************************************

  While @@FETCH_STATUS = 0

  begin

  if @Statisticvalue <=100

  Begin

  and does the 2nd 'end' go with the last end?

  thanks Craig

• Gayathri.Varadarajan

  SSCrazy

  Points: 2080

  More actions

  March 22, 2010 at 1:21 am

  #1137430

  yes u r right 🙂

• craig 84462

  Say Hey Kid

  Points: 669

  More actions

  March 22, 2010 at 1:29 am

  #1137433

  cheers mate, very much if I can help let me know

  what do you work in?