[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