问题描述

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

此代码在从 cc2 获取下一个到 @schedindate，@TimeInR"行中出现错误我的错在哪里?我找不到它..谢谢..

this code gives error in the line "fetch next from cc2 into @schedindate, @TimeInR" where's my fault? i can't find it.. Thank you..

推荐答案

试试这个 -

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

这篇关于Cursorfetch:INTO 列表中声明的变量数量必须与所选列的数量匹配的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持编程学习网！