[thelist] MSSQL Cursor help

Brian Cummiskey Brian at hondaswap.com
Tue Aug 16 15:57:50 CDT 2005


Hey all, I'm pulling my hair out on this one.

I'm trying to write a stored procedure to kill some records.  The 
problem being, is that they are in multiple tables.  I have a "jobs" 
table, in which, i create my cursor list from, and then, i try to 
perform the cycle through of select * from @thecurrent_table where @params.

Basically, the jobs table is an index of all the tables in this 
particular database.

For example,

table jobs:

job1 date
job2 date


table job1:
recordid, etc

table job2:
recordid, etc

The code works, but it doesn't seem to cycle.  It always stops after the 
first job table.

useless code omitted:
----------------------------------

declare DNCList cursor for
	SELECT job AS 'joblist'
	FROM jobs
	WHERE --etc

OPEN DNCList
	FETCH NEXT FROM DNCList into @joblist
		while @@fetch_status = 0
		begin
			set @query = 'SELECT * FROM ' + @joblist  --etc
		end

		exec(@query)

		FETCH NEXT FROM DNCList into @joblist
	CLOSE DNCList
DEALLOCATE DNCList

-------------------------------

anyone see anything wrong?

obviously, my vars are declared and things are passing in as supposed 
to.  my problem is on the cursor loop.



More information about the thelist mailing list