Bind Variable in CURSOR

SQL Server 2000 SP4 with AWE hotfix. Windows 2003 SP1.

I have a stored procedure which is not working the way I think it
should be.

I have a CURSOR which has a variable in the WHERE clause:

DECLARE get_tabs CURSOR local fast_forward FOR
SELECT distinct tablename, id, shcontig1dt, shcontig2dt
FROM db_ind
WHERE dbname = @dbname
ORDER BY tablename

It won't return anything, even when I verify that @dbname has a value
and if I run the query in Query Analyzer with the value, it returns
rows:

SELECT distinct tablename, id, shcontig1dt, shcontig2dt
FROM db_ind
WHERE dbname = 'Archive'
ORDER BY tablename

 DB_Rpt_Fragmentation
	1157579162	2006-03-29 09:52:11.777	2006-03-29
09:52:11.823
 DtsAdtStdArchive_Dat
 aSourceType	51757688
2	2006-03-29
09:52:11.870	2006-03-29 09:52:11.887
 DtsADTstdArchiveNotU
sed	357576312	2006-03-29 09:52:11.887	2006-03-29
09:52:12.103



I've taken out most of the guts for simplicity, but here's what I've
got:



--CREATE TABLE dbo.db_ind
--(
-- db_ind_tk		int	IDENT
ITY,
--    id          int      NULL,
--    tablename   sysname  NOT NULL,
--    indid       int      NULL,
--    indexname   sysname  NOT NULL,
--    shcontig1dt datetime NULL,
--    defragdt    datetime NULL,
--    shcontig2dt datetime NULL,
--    reindexdt   datetime NULL
--)


ALTER     PROCEDURE IDR
(	@hours 	int
)
AS

--SET NOCOUNT ON
--SET ANSI_WARNINGS OFF

DECLARE @tabname 	varchar(100),
@indname 	varchar(100),
@dbname 	varchar(50),
@vsql 		varchar(1000),
@v_hours 	varchar(4),
 @shcontig1dt	datetim
e,
 @shcontig2dt	datetim
e,
@defragdt	datetime,
 @reindexdt	datetime,

@id		int,
@indid		int,
@rundbcursor	int,
@runtabcursor	int,
@runindcursor	int

DECLARE get_dbs CURSOR local fast_forward FOR
SELECT dbname
FROM db_jobs
WHERE idrdate < getdate() - 4
or idrdate is null
ORDER BY dbname

DECLARE get_tabs CURSOR local fast_forward FOR
SELECT distinct tablename, id, shcontig1dt, shcontig2dt
FROM db_ind
WHERE dbname = @dbname
ORDER BY tablename

DECLARE get_inds CURSOR local fast_forward FOR
SELECT indid, indexname, defragdt, reindexdt
FROM db_ind
WHERE dbname = @dbname
AND tablename = @tabname
ORDER BY indexname

OPEN get_dbs
FETCH NEXT FROM get_dbs
INTO @dbname

IF @@FETCH_STATUS = 0
SELECT @rundbcursor = 1
ELSE
SELECT @rundbcursor = 0

SELECT @v_hours =  CONVERT(varchar,@hou
rs)

-- ====================
 ====================
 ====================
==============
 ====================
===========
-- ====================
 ====================
 ====================
==============
 ====================
===========
-- ====================
 ====================
 ====================
==============
 ====================
===========

WHILE  @rundbcursor = 1
BEGIN  -- db while

PRINT  '===================
=========='
PRINT @dbname
PRINT  '===================
=========='

-- ====================
 ====================
 ====================
==============
 ====================
===========
-- ====================
 ====================
 ====================
==============
 ====================
===========

OPEN get_tabs

FETCH NEXT FROM get_tabs
INTO @tabname, @id, @shcontig1dt, @shcontig2dt

IF @@FETCH_STATUS = 0
BEGIN
PRINT 'table: ' + @tabname
SELECT @runtabcursor = 1
end
ELSE
BEGIN
PRINT 'not getting any tables! '			--  <<<<<  THIS IS WHERE IT HITS
SELECT @runtabcursor = 0
end

WHILE  @runtabcursor = 1
BEGIN
PRINT @dbname
PRINT @tabname

-- ====================
 ====================
 ====================
==============
 ====================
===========

OPEN get_inds
FETCH NEXT FROM get_inds
INTO @indid, @indname, @defragdt, @reindexdt

IF @@FETCH_STATUS = 0
SELECT @runindcursor = 1
ELSE
SELECT @runindcursor = 0

WHILE @runindcursor = 1
BEGIN
PRINT 'Index:' + @dbname + '.' + @tabname + '.' + @indname

FETCH NEXT FROM get_inds
INTO @indid, @indname, @defragdt, @reindexdt

IF @@FETCH_STATUS = 0
SELECT @runindcursor = 1
ELSE
SELECT @runindcursor = 0

END	-- 1st loop through indexes
CLOSE get_inds

-- ====================
 ====================
 ====================
==============
 ====================
===========

--==========
PRINT 'db.tab: ' + @dbname + '.' + @tabname

--==========

-- ====================
 ====================
 ====================
==============
 ====================
===========

OPEN get_inds
FETCH NEXT FROM get_inds
INTO @indid, @indname, @defragdt, @reindexdt

IF @@FETCH_STATUS = 0
SELECT @runindcursor = 1
ELSE
SELECT @runindcursor = 0

WHILE @runindcursor = 1
BEGIN

PRINT 'dbname: ' + @dbname
PRINT 'tabname: ' + @tabname
PRINT 'indname: ' + @indname

FETCH NEXT FROM get_inds
INTO @indid, @indname, @defragdt, @reindexdt

IF @@FETCH_STATUS = 0
SELECT @runindcursor = 1
ELSE
SELECT @runindcursor = 0

END -- 2nd loop through indexes
CLOSE get_inds

-- ====================
 ====================
 ====================
==============
 ====================
===========

FETCH NEXT FROM get_tabs
INTO @tabname, @id, @shcontig1dt, @shcontig2dt

IF @@FETCH_STATUS = 0
SELECT @runtabcursor = 1
ELSE
SELECT @runtabcursor = 0

END	-- loop through tables
CLOSE get_tabs

-- ====================
 ====================
 ====================
==============
 ====================
===========
-- ====================
 ====================
 ====================
==============
 ====================
===========


PRINT 'Index Maintenence complete.  Job report in
& #91;DB_Rpt_Fragmenta
tion]'
PRINT ''

FETCH NEXT FROM get_dbs
INTO @dbname

IF @@FETCH_STATUS = 0
SELECT @rundbcursor = 1
ELSE
SELECT @rundbcursor = 0

END -- loop through databases
CLOSE get_dbs
deallocate get_dbs
deallocate get_tabs
deallocate get_inds

-- ====================
 ====================
 ====================
==============
 ====================
===========
-- ====================
 ====================
 ====================
==============
 ====================
===========
-- ====================
 ====================
 ====================
==============
 ====================
===========

GO





And this is what I'm getting:

 ====================
=========
Archive
 ====================
=========

(0 row(s) affected)

not getting any tables!
Index Maintenence complete.  Job report in & #91;DB_Rpt_Fragmenta
tion]

.
.
.
etc.


Am I missing something obvious?

Thank you for any help you can provide!!

One of my fellow emps got it - apparently the CURSOR needed to be
declare w/in the loop right before I opened it.

I moved the get_tabs and get_inds cursor declarations and all is well .
. .