Howdy, Stranger!

It looks like you're new here. If you want to get involved, click one of these buttons!

Categories

Must Declare the variable

shadab8shadab8 Member Posts: 1
I am trying to create the procedure below, it just fecthes the tablelist in the current database with number of rows and creation date for each table. But I cannot fire an SQL statement which supplies a tablename dynamically. I am getting error
Must declare the variable '@tablename' at line 9


create procedure ShowTables1 as
declare @tablename varchar(30), @crdate datetime, @iCount integer
declare abc cursor fast_forward for
select cast(a.name as varchar(30)) as TableName, crdate from sysobjects a where type='U'
open abc
fetch next from abc into @tablename, @crdate
@fetch_status=0
begin
Set @iCount = (Select count(*) from @tablename)
print @tablename + ' ' + @iCount + ' ' + @crDate
fetch next from abc into @tablename, @crdate
end
close abc
deallocate abc

Comments

  • WolfsbaneWolfsbane Member Posts: 12
    Interesting. The problem lies in the attempt to set @icount to to COUNT(*) on a variable. COUNT() can only be used on a table. But you are going to have even more problems. You are trying to apply a string function to an INT and a DATETIME column this will bomb as well.

    I have done a bit of alteration to your code :
    CREATE PROCEDURE ShowTables1
    AS
    DECLARE @tablename varchar(30), @crdate datetime, @iCount integer
    DECLARE abc cursor fast_forward FOR
    SELECT cast(a.name as varchar(30)) as TableName, crdate
    FROM sysobjects a
    WHERE a.type = 'U'
    OPEN abc
    FETCH NEXT FROM abc INTO @tablename, @crdate
    @fetch_status=0
    begin
    /*Set @iCount = (Select count(*) from @tablename)*/
    print @tablename + ' ' + CAST(@iCount as varCHAR (30)) + ' ' + CAST( @crDate as varCHAR)
    fetch next from abc into @tablename, @crdate
    end
    close abc
    deallocate abc

    You will notice I comented out the SET statement for @icount and changed the column type with a cast statement. Good luck with the rest.
    : I am trying to create the procedure below, it just fecthes the tablelist in the current database with number of rows and creation date for each table. But I cannot fire an SQL statement which supplies a tablename dynamically. I am getting error
    : Must declare the variable '@tablename' at line 9
    :
    :
    : create procedure ShowTables1 as
    : declare @tablename varchar(30), @crdate datetime, @iCount integer
    : declare abc cursor fast_forward for
    : select cast(a.name as varchar(30)) as TableName, crdate from sysobjects a where type='U'
    : open abc
    : fetch next from abc into @tablename, @crdate
    @fetch_status=0
    : begin
    : Set @iCount = (Select count(*) from @tablename)
    : print @tablename + ' ' + @iCount + ' ' + @crDate
    : fetch next from abc into @tablename, @crdate
    : end
    : close abc
    : deallocate abc
    :

Sign In or Register to comment.