[thelist] MSSQL Cursor help

Phil Turmel philip at turmel.org
Thu Aug 18 15:02:05 CDT 2005


Brian Cummiskey wrote:
[snip]
> 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.
> 
Brian,

Try placing the exec and 2nd fetch *inside* the loop like so:

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


HTH,

Phil


More information about the thelist mailing list