none
Must declare the table variable

    Question

  • Hi guys, I'm having trouble with thuis situation:

    create table Checkcolumn (tabledb nchar(30), columndb nchar (30), mxsize int, acgsize int)
    declare @table2 char(20)
    declare @column2 char (30)
    declare @maxsize char (4)
    declare @avgsize char (4)
    select  @table2=o.name, @column2=s.name from sys.columns s inner join sys.objects o on s.object_id=o.object_id 
    
    select @maxsize=max(len(@column2)),@avgsize=avg(len(@column2)) from @table2
    

    The error is: must declare the table variable.

    this is only a part of a complex stored procedure but I'm locking just there...I got already @table2 and @column2 but I need also @maxsize and @avgsize to fill another table...

    Saturday, October 19, 2013 2:25 PM

Answers

  • The error is due to the concatenation of string integer in 'select '+@max. here @max is an integer and you have to convert to string inorder to concatenate. I have made few other corrections to your script and the same is pasted below. You can try this one and let me know how it goes. 

    printing the dynamic sql before executing helps to debug any issues with dynamic sql formation. i would first print the dynamic sql even before executing. you can use PRINT @tostart to print. Besides use ( ) with EXEC statement when executing a dynamic sql like EXEC(@tostart)

    declare @table2 varchar(30)
    declare @column2 varchar (30)
    declare @max int
    declare @avg int
    declare @tostart nvarchar(512)
    select top 1 @table2=tabledb, @column2=columndb from checkcolumn where upd=1
    set @tostart = 'select ''' + @table2 + ''', ''' + @column2 + ''', max(len('+@column2+')), avg(len('+@column2+'))  from '+@table2 
    INSERT INTO checkcolumnfinal (tablecheck, columncheck, maxlenght, avglenght) 
    exec(@tostart)
    

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

    Please mark as answered if a post solves your problem and vote if you find it helpful.


    --sIbu

    • Marked as answer by DIEGOCTN Sunday, October 20, 2013 9:58 AM
    Saturday, October 19, 2013 11:26 PM

All replies

  • It is giving you the right error message. The last SELECT statement is using @table2 as a Table. And you have declare @table2 as a character data type which is not correct.

    Also you can not use @table2 in the SELECT statement directly, it will only work when you implement in dynamic sql.


    Regards, RSingh

    Saturday, October 19, 2013 2:38 PM
  • I know it's the right message, just I'm working with different languages and I'm having a brain break...any suggestion? If I build a dynamic select it doesn't returne @avgsize and @maxside...
    Saturday, October 19, 2013 2:46 PM
  • We can use sp_executesql procedure. For detail refer this link

    http://technet.microsoft.com/en-us/library/ms188001.aspx

    Example,

    declare @SqlString nvarchar(2000)
    declare @ParamDef nvarchar(2000)
    set @SqlString = N'exec proc1 @param1, @param2, @param3'
    set @ParamDef = N'@param1 bit, @param2 bit, @param3 bit'
    EXECUTE sp_executesql @SqlString ,@ParamDef, @param1 = 0, @param2 = 1, @param3 = 1

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

    Are you trying to get the column datalengths for each tables ?

    select o.name tabledb, max(datalength(s.name)) mxsize,avg(datalength(s.name)) avgsize 
    from sys.columns s inner join sys.objects o on s.object_id=o.object_id
    where o.name = 'ORDER'
    group by o.name


    Regards, RSingh


    • Edited by RSingh() Saturday, October 19, 2013 3:57 PM
    Saturday, October 19, 2013 3:19 PM
  • Something like this but I want take the avg and max directly from the table:

    declare @table2 varchar(8000)
    declare @column2 char (30)
    declare @maxsize char (4)
    declare @avgsize char (4)
    select  @table2=o.name, @column2=s.name from sys.columns s inner join sys.objects o on s.object_id=o.object_id inner join sys.types t on s.system_type_id=t.system_type_id
    where o.type='U' and o.object_id=885578193 and t.system_type_id in (35,99,167,175,231,239) and t.name <> 'sysname'
    declare @tostart varchar(8000)
    set @tostart='select '+@maxsize+'=max(len('+@column2+')),'+@avgsize+'=avg(len('+@column2+')) from'+ @table2
    exec (@tostart)
    print @avgsize

    In this case I don't have error but @avgsize is blank. 

    select @maxsize=max(len(englishproductname)), @avgsize=avg(len(englishproductname)) FROM dimproduct
    print @maxsize
    print @avgsize
    In this other case it returns the right value...
    Saturday, October 19, 2013 3:28 PM
  • Still i do not understand what are you trying to achieve. Can you please explain what are you tring to achieve. The SELECT query to assign table name is not correct. Below query will return multiple table names or rows and you can not assign to a variable i.e @table2.

    select o.name, s.name from sys.columns s
    inner join sys.objects o on s.object_id=o.object_id inner join sys.types t
    on s.system_type_id=t.system_type_id
    where o.type='U' and o.object_id=885578193 and
    t.system_type_id in (35,99,167,175,231,239) and t.name <> 'sysname'


    Regards, RSingh



    • Edited by RSingh() Saturday, October 19, 2013 3:58 PM
    Saturday, October 19, 2013 3:55 PM
  • In fact I'm going to use the cursor...just I'm struggling trying to pass the variable @table2. But I'll come up with some situation. Thanks
    Saturday, October 19, 2013 7:35 PM
  • Sorry guys, I'm really close to the final, just, within a real complex sp, I got this error:

    Conversion failed  when converting value varchar 'select' in type data int

    begin declare @table2 varchar(30) declare @column2 char (30) declare @max int declare @avg int select top 1 @table2=tabledb, @column2=columndb from checkcolumn where upd=1 declare @tostart nvarchar(512)='select '+@max+'=max(len('+@column2+')),'+@avg+'=avg(len('+@column2+')) from '+@table2 exec @tostart INSERT INTO checkcolumnfinal (tablecheck, columncheck, maxlenght, avglenght) values (@table2, @column2, cast(@max as int), cast(@avg as int))


    The problem is in the dynamic select. Now the value is given one to one, so the query shold works correctly. Just I don't known how to set the @max and @avg to insert into checkcolumnfinal.

    Any suggestion?

    Sorry the DDL:

    create table Checkcolumn (iddnum int identity, tabledb nchar(30), columndb nchar (30), maxlenght int, typename char(20),  upd int default (1) )
    
    create table Checkcolumnfinal (tablecheck nchar(30), columncheck nchar (30), maxlenght int, avglenght int )




    • Edited by DIEGOCTN Saturday, October 19, 2013 10:20 PM
    Saturday, October 19, 2013 10:16 PM
  • The error is due to the concatenation of string integer in 'select '+@max. here @max is an integer and you have to convert to string inorder to concatenate. I have made few other corrections to your script and the same is pasted below. You can try this one and let me know how it goes. 

    printing the dynamic sql before executing helps to debug any issues with dynamic sql formation. i would first print the dynamic sql even before executing. you can use PRINT @tostart to print. Besides use ( ) with EXEC statement when executing a dynamic sql like EXEC(@tostart)

    declare @table2 varchar(30)
    declare @column2 varchar (30)
    declare @max int
    declare @avg int
    declare @tostart nvarchar(512)
    select top 1 @table2=tabledb, @column2=columndb from checkcolumn where upd=1
    set @tostart = 'select ''' + @table2 + ''', ''' + @column2 + ''', max(len('+@column2+')), avg(len('+@column2+'))  from '+@table2 
    INSERT INTO checkcolumnfinal (tablecheck, columncheck, maxlenght, avglenght) 
    exec(@tostart)
    

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

    Please mark as answered if a post solves your problem and vote if you find it helpful.


    --sIbu

    • Marked as answer by DIEGOCTN Sunday, October 20, 2013 9:58 AM
    Saturday, October 19, 2013 11:26 PM