Cursor, cursor, cursor - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Cursor, cursor, cursor

Author

Topic

dwjongbloed
Starting Member

9 Posts

Posted - 2009-10-29 : 11:19:02

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

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'.

yosiasz
Master Smack Fu Yak Hacker

1635 Posts

Posted - 2009-10-29 : 11:49:33

you can't do as Select COUNT(*) AS @coffeebreaktime

do
select @exp_rows = count(*)
from dw_data_warehouse.dbo.@tablename
where DW_Eff_End_Dt = '12/31/3030'

select @active_rows = count(*) from dw_data_warehouse.dbo.@tablename
where DW_Eff_End_Dt <> '12/31/3030'

<><><><><><><><><><><><><><><><><><><><><><><><><>
If you don't have the passion to help people, you have no passion

khalik
Constraint Violating Yak Guru

443 Posts

Posted - 2009-10-29 : 11:51:50

Cursors are slow, i hate!
try using tables variable or temp tables to do batch process.

the problem in ur case is
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'

trying using select @active_rows = count(*) .....

========================================
Project Manager who loves to code.
===============
Ask to your self before u ask someone

robvolk
Most Valuable Yak

15732 Posts

Posted - 2009-10-29 : 11:53:19

You also can't update a table if there are no rows in it (truncate table with no insert afterwards).

Here's a snippet that should do the trick:

truncate table damon_work.dbo.DW_Table_Rowcount_stats

exec dw_data_warehouse.dbo.sp_msforeachtable 'insert damon_work.dbo.DW_Table_Rowcount_stats(table_name, expired_rows, Active_rows)
select parsename(''?'',1), sum(case when a.DW_Eff_End_Dt=''12/31/3030'' then 1 end) expired, 
sum(case when a.DW_Eff_End_Dt<>''12/31/3030'' then 1 end) active
from dw_data_warehouse.? as a'

Warning: not tested on actual data. If it works first time, you owe me a beer.

dwjongbloed
Starting Member

9 Posts

Posted - 2009-10-29 : 14:21:44

Thanks robvolk - worked like a charm

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources