problem with dynamci sql

[washaw]

hello,

I have a table tbl (have identity column) contains name of tables, and need to do count the number of records for each table
--code

declare @int int, @str varchar(1000), @tblName varchar(100)

select @int = min(id) from tbl

while @int is not null
begin
   select @tblName = tblName from tbl where id = @id
   set @str = 'select * into #tmpTable from ' + @tableName
   
   select count(*) from #tmpTable

   select @int = min(id) from tbl where id > @id

   drop table #tmpTable
end

it is erroring out invalid object name #tmpTable

is it possible to use temporary table in dynamic sql if not what should I do

Thanks

 

[SQLSister]

YOu would have to put all the code in the dynamic part. However dynamic sql is a not really a good choice for most things. It is less secure and performs more slowly. Why do you need to be able to vary the table name? Unless this is an administrative task, I would suggest that creating a separate proc for each table is a better choice. You need to read up on SQL injection attacks and other issues concerning why dynamic SQl is often a very poor choice.

http://www.sommarskog.se/dynamic_sql.html

And why are you bothering with a temp table anyway? WHy not just do a count from the real table? Oh and your variables names don't track through that. Where did @tablename come from?

"NOTHING is more important in a database than integrity." ESquared

 

[washaw]

Thank you for your suggestion SqlSister,

what I am not quite understanding is say
I have so many tables in my database and I want to get the number of records only for those tables whose names are in the table say tbl; tbl looks like this

id name_of_table
1 tbl_table1
2 tbl_table2
3 tbl_table3

I want to get the number of records onl;y for these tables in this case tbl_table1, tbl_table2, tbl_table3, how can I do it without writing dynamic sql

Thanks,

 

[k01]

There might be better way.
But anyway I'll show you the sample with dynamic SQL.

CREATE TABLE [dbo].[TEST](
  [TableName] VARCHAR(255) NOT NULL,
  [NrOfRecords] [int] NOT NULL
) ON [PRIMARY]


  DECLARE @TABLE_NAME VARCHAR(255)
  DECLARE @SQL        VARCHAR(8000)

  DECLARE C_TABLE CURSOR FOR 
  SELECT 
    name_of_table 
  FROM 
    tbl 
  ORDER BY 
    name_of_table

  OPEN C_TABLE
  FETCH NEXT FROM C_TABLE INTO @TABLE_NAME

  WHILE @@FETCH_STATUS = 0
  BEGIN
    FETCH NEXT FROM C_TABLE INTO @TABLE_NAME
  
    PRINT @TABLE_NAME

    SET @SQL = 
        '
          INSERT INTO TEST
          SELECT ''' +
            @TABLE_NAME + ''' AS TableName
            ,COUNT(*) AS NrOfRecords
          FROM ' +
            @TABLE_NAME
        
        EXEC (@SQL)
  END

  CLOSE C_TABLE

And one more,
This is the sample to count the number of records at each table on the database.

http://aboutabi.blogspot.com/2008/03/sqlserver-how-to-count-number-of.html

koichi

http://aboutabi.blogspot.com/