试试这个-
DECLARE @empid INT , @schedindate DATETIME , @ss NVARCHAr(100) , @indice NVARCHAr(2) , @FromDate DATETIME , @ToDate DATETIME , @TimeInR DATETIME , @TimeOutR DATETIMESELECt @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_timecardWHERe schedindate BETWEEN @FromDate AND @ToDateDECLARE @ids TABLE(id BIGINT IDENTITY(1,1), emp BIGINT)INSERT INTO @ids (emp)SELECt DISTINCT empidFROM @tempINSERT INTO dbo.ta_MonthlyAttendance(id, EmpID)SELECt id, empFROM @idsDECLARE cc CURSOR LOCAL FAST_FORWARD READ_onLY FOR SELECt DISTINCT empid FROM @tempOPEN ccFETCH NEXT FROM cc INTO @empidWHILE (@@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 @empidENDCLOSE ccDEALLOCATE cc
