Cursor, Cursor, Cursor - Need help

Question

Post reply

Cursor, Cursor, Cursor - Need help

dwjongbloed

SSC Eights!

Points: 943
More actions
October 29, 2009 at 8:14 am

#206088

What I'm trying to do is create a table that has the tables with in a certain Database on my server. This table will contain the table names, active rows, and expired rows. The active and expired rows will be done by performing a select count(*) where xxxx = ' '. I have a cursor started to do this but an running into problems. The code I have looks like this:
truncate table damon_work.dbo.DW_Table_Rowcount_stats
declare @table_name varchar(100)
declare @exp_rows numeric(18,0)
declare @active_rows numeric(18,0)
declare objects CURSOR
for
select name
from Dw_data_warehouse.dbo.sysobjects
where Dw_data_warehouse.dbo.sysobjects.xtype = 'U'
order by dw_data_warehouse.dbo.sysobjects.name
--declare @table_name varchar(100)
--declare @exp_rows numeric(18,0)
--declare @active_rows numeric(18,0)
open objects
FETCH NEXT From objects into @table_name
WHILE (@@FETCH_STATUS=0)
BEGIN
select count(*) as @exp_rows
from dw_data_warehouse.dbo.@tablename
where DW_Eff_End_Dt = '12/31/3030'
select count(*) as @active_rows
from dw_data_warehouse.dbo.@tablename
where DW_Eff_End_Dt <> '12/31/3030'
begin
update damon_work.dbo.DW_Table_Rowcount_stats
set table_name = @table_name,
expired_rows = @exp_rows,
Active_rows = @active_rows
end
FETCH NEXT From objects into @table_name
end
close objects
deallocate objects
The problem I'm having is with the select statements within the OPEN/ Fetch area. I get the following error msg's:
Msg 170, Level 15, State 1, Line 30
Line 30: Incorrect syntax near '@exp_rows'.
Msg 170, Level 15, State 1, Line 34
Line 34: Incorrect syntax near '@active_rows'.
Any help will be greatly appreciated on this. Also posting this in teh SQL2005 forum to see if any advice from them.