Cursorfetch:Le nombre de variables déclarées dans le DANS la liste doit correspondre à celui des colonnes sélectionnées

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

ce code provoque une erreur à la ligne "fetch next à partir du ce2 en @schedindate, @TimeInR"
où est ma faute? je ne le trouve pas..
Je vous remercie..

OriginalL'auteur Nejthe | 2013-05-28

  1. 2

    Essayez celui -

    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
    Merci beaucoup, je ne peux pas utiliser la fonction Row_Number() avez-vous une idée pourquoi ?
    Quelle est votre version de SQL Server?
    sql server 2000, il n'existe pas dans cette version
    S'il vous plaît essayer la mise à jour de réponse.

    OriginalL'auteur Devart

  2. 4
    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

    Vous sélectionnez schedindate, TimeInR, TimeOutR mais la into a seulement @schedindate, @TimeInR

    Raj

    oh je ne l'avais pas vu merci beaucoup.. je vais juste demander une chose de plus, je suis maintenant, obtenir une liste des erreurs où le nombre 0 incrément de 1 nombre dans chaque message d'erreur: Serveur: Msg 170, Niveau 15, État 1, Ligne 1 Ligne 1: syntaxe Incorrecte près de '0'.

    OriginalL'auteur Raj