Cursorfetch: the number of variables declared in the INTO list should match the number of selected columns

Question

declare @id int declare @empid int set @id = 0 declare @schedindate datetime declare @ss nvarchar(100) declare @indice nvarchar(2) declare @FromDate datetime declare @ToDate datetime declare @TimeInR datetime declare @TimeOutR datetime set @FromDate = '2009-01-14' set @ToDate = '2010-01-30' Declare cc cursor for select distinct empid from ta_timecard where schedindate between @FromDate and @ToDate open cc fetch next from cc into @empid while (@@fetch_status = 0) begin set @id = @id + 1 insert into ta_MonthlyAttendance (ID, EmpID) values (@id, @empid) declare cc2 cursor for select distinct schedindate, TimeInR, TimeOutR from ta_timecard where empid = @empid and schedindate between @FromDate and @ToDate open cc2 fetch next from cc2 into @schedindate, @TimeInR while (@@fetch_status = 0) begin set @indice = cast(datediff(day, @fromdate, @schedindate) as nvarchar(4)) set @TimeInR = (select TOP 1 ta_TimeCard.TimeInR from ta_TimeCard where (@schedindate between @FromDate and @ToDate) and EmpID=@empid ) set @schedindate = (select TOP 1 ta_TimeCard.SchedInDate from ta_TimeCard where (@schedindate between @FromDate and @ToDate) and empid=@empid ) set @ss = 'update ta_MonthlyAttendance set NOD ' + @indice + ' = + dbo.ta_dayofweek('+ char(39) + convert(nvarchar(50), @schedindate, 102) + char(39) +' ) , TimeInR ' + @indice + ' = + @TimeInR + where empid = ' + cast(@empid as nvarchar(20)) execute sp_executesql @ss fetch next from cc2 into @schedindate, @TimeInR end close cc2 deallocate cc2 fetch next from cc into @empid end close cc Deallocate cc 

this code gives an error in the line "fetch from cc2 to @schedindate, @TimeInR" where is my fault? I can’t find him .. Thanks ..

Answer 1

Try this option -

 DECLARE @empid INT , @schedindate DATETIME , @ss NVARCHAR(100) , @indice NVARCHAR(2) , @FromDate DATETIME , @ToDate DATETIME , @TimeInR DATETIME , @TimeOutR DATETIME SELECT @FromDate = '20090114' , @ToDate = '20100130' DECLARE @temp TABLE ( schedindate DATETIME , TimeInR VARCHAR(10) , TimeOutR VARCHAR(10) , empid INT ) INSERT INTO @temp (schedindate, TimeInR, TimeOutR, empid) SELECT DISTINCT schedindate , TimeInR , TimeOutR , empid FROM dbo.ta_timecard WHERE schedindate BETWEEN @FromDate AND @ToDate DECLARE @ids TABLE(id BIGINT IDENTITY(1,1), emp BIGINT) INSERT INTO @ids (emp) SELECT DISTINCT empid FROM @temp INSERT INTO dbo.ta_MonthlyAttendance(id, EmpID) SELECT id, emp FROM @ids DECLARE cc CURSOR LOCAL FAST_FORWARD READ_ONLY FOR SELECT DISTINCT empid FROM @temp OPEN cc FETCH NEXT FROM cc INTO @empid WHILE (@@fetch_status = 0) BEGIN SELECT @indice = CAST(DATEDIFF(DAY, @fromdate, t.SchedInDate) AS NVARCHAR(4)) , @TimeInR = t.TimeInR , @schedindate = t.SchedInDate FROM @temp t WHERE empid = @empid SELECT @ss = 'update ta_MonthlyAttendance set NOD ' + @indice + ' = + dbo.ta_dayofweek(' + CHAR(39) + CONVERT(NVARCHAR(50), @schedindate, 102) + CHAR(39) + ' ) , TimeInR ' + @indice + ' = ' + @TimeInR + ' where empid = ' + CAST(@empid AS NVARCHAR(20)) EXEC sys.sp_executesql @ss FETCH NEXT FROM cc INTO @empid END CLOSE cc DEALLOCATE cc 

Answer 2

 declare cc2 cursor for select distinct schedindate, TimeInR, TimeOutR from ta_timecard where empid = @empid and schedindate between @FromDate and @ToDate open cc2 fetch next from cc2 into @schedindate, @TimeInR 

You select schedindate, TimeInR, TimeOutR , but the in operator has only @schedindate, @TimeInR

Rajah