[thelist] MSSQL Cursor help

Mattias Thorslund mattias at inreach.com
Thu Aug 18 15:11:09 CDT 2005


Looks like your loop is wrong. I think the "end" should be after the 
second "FETCH NEXT".

It's been a while since I did MSSQL stored procedures. But I recall that 
people tend to use cursors WAY too often (they don't have great 
performance), and they can on 99% of the times be re-written into a 
set-based format. I admit that this one might be in the 1%, though, and 
for a "maintenance" function, the performance probably isn't important 
to warrant putting more time into it than to make it work.

/Mattias

Brian Cummiskey wrote:

> 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 views at http://www.thorslund.us




More information about the thelist mailing list