Cursor fetch Error

[rvancleef]

I am trying to execute a SQL Job with a cursor, and it fails when I add the usertypes.usertype field and @utypeXYZ variable. The error message is:

"Cursor fetch: The number of variables declared in the INTO list must match that of selected columns."

Here is the snippet of code I am changing:

CREATE PROCEDURE update_system_alerts
AS
declare
@tmpint int,
@mymessage varchar(500),
@mystudentrecs int,
@myuserrecs int,
@mynewrecs int,
@myalertcount int,
@visits int
declare
@userid varchar(20),
@utitle varchar(50),
@ufname varchar(50),
@umiddleinit varchar(50),
@ulname varchar(50),
@uemail varchar(50),
@ubarcodeid varchar(30),
@ustatus varchar(20),
@udob varchar(20),
@umedicalclear varchar(20),
@uparqstatus varchar(20),
@umedicalstatus varchar(20),
@uprimaryid varchar(20),
@contractid varchar(10),
@enddate varchar(20),
@startdate varchar(20),
@category varchar(20),
@cstatus varchar(20),
@utypeXYZ varchar(50),
@balance money
declare @myerror int
declare @monthago smalldatetime
set @monthago = dateadd(month,-1,getdate())
set nocount on
print 'Start system alert process - update_system_alerts ' + convert(varchar,getdate())
declare @myrec Cursor
/* select all user recs and their contract and visit information. Whatever is needed to check them over. */
Set @myrec = Cursor for
select
users.userid,
users.barcodeid,
users.status,
users.fname,
users.lname,
users.email,
users.dob,
users.medicalClear,
users.parqstatus,
users.medicalstatus,
users.primaryid,
usertypes.usertype
from users
inner join usertypes on users.userid = usertypes.userid
open @myrec
fetch next from @myrec
into @userid,
@ubarcodeid,
@ustatus,
@ufname,
@ulname,
@uemail,
@udob,
@umedicalclear,
@uparqstatus,
@umedicalstatus,
@uprimaryid,
@utypeXYZ
set @myuserrecs = 0
set @myalertcount = 0
...
close @myrec
deallocate @myrec

 

[tlbroadbent]

Is there another Fetch statement in the code you didn't post? Usually cursors are used in a loop and fetches are repeated to the end of the cursor record set.

If you want to get the best answer for your question read faq183-874 and faq183-3179.
Terry L. Broadbent - DBA
SQL Server Page:

http://tlbroadbent.home.comcast.net/sql/sql_articles.htm

 

[rvancleef]

Thanks!

You are correct. I found another insert in fact and when updated everything is working perfectly.

 

[SQLSister]

And why on earth are you doing an insert using a cursor anyway? Cursors are performance hogs and inserts virtually never should use them.

Questions about posting. See faq183-874