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_DataSourceType 517576882 2006-03-29
09:52:11.870 2006-03-29 09:52:11.887
DtsADTstdArchiveNotUsed 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 IDENTITY,
-- 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 datetime,
@shcontig2dt datetime,
@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,@hours)

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

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
[DB_Rpt_Fragmentation]'
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 [DB_Rpt_Fragmentation]

..
..
..
etc.


Am I missing something obvious?

Thank you for any help you can provide!!

.

Relevant Pages

  • Use one variable in the FROM clause
    ... declare dbname_cur cursor for ... fetch next from dbname_cur into @dbname ... Must declare the variable '@dbname'.) ...
    (microsoft.public.sqlserver.server)
  • Help Needed
    ... i m using following cursor for backing up all databases. ... DECLARE @dbname VARCHAR ... FETCH NEXT FROM bak_cursor INTO @dbname ...
    (microsoft.public.sqlserver.programming)
  • Re: Multiple db query call from within different context into #temp table
    ... different context and still insert into a #temp table. ... DECLARE @exec_context varchar ... ORDER BY DBNAME ... FETCH NEXT FROM companies_cursor INTO @DBNAME ...
    (comp.databases.ms-sqlserver)
  • Re: SQLite3; weird error
    ... the state of dbname and assigns it to variable foo. ... you then need to create a cursor object by ... cursor object's method execute() with the SQL query as a quoted ...
    (comp.lang.python)
  • Re: Problem with Stored Proc performance after Weekly Index Re-Org Job
    ... CREATE TABLE #SQLIndexStatsCommands ( ... SELECT AS DBName FROM master.dbo.sysdatabases AS A ... FETCH NEXT FROM db_Update INTO @dbName ... DECLARE @SQLCommand varchar ...
    (microsoft.public.sqlserver.server)