|
kimharlan
Starting Member
9 Posts |
 Posted - 2007-04-20 : 08:23:51
|
| Hello! This is the first time I have ever used a Temp Table, as well as a cursor, so that is probably why I can't figure this out. I am basically taking a result set and storing it (along with a new field called "ChangeColor", which initially has a NULL value) in a temp table. Then I use a cursor to iterate through each row in the temp table and update the field "ChangeColor", one row at a time, according to some nasty business logic. I am planning on putting this whole thing into a stored procedure once it is working and returning the results to my ASP.Net page, so that I can color-code rows that are on different pages of my Gridview (according to the "ChangeColor" field I created in my SQL temp table). I have consulted a senior developer and he thinks that using SQL--specifically using a cursor) would be the best way to handle this situation. Of course I know that you want to avoid cursors whenever possible, but I think this situation really warrants the use of one. Anyway, I don't understand why my code is not working. The results are being returned with a NULL value for the "ChangeColor" field, which means the cursor is not updating the field like it is supposed to. I think I may have the cursor syntax wrong somewhere (although I am not getting a syntax error and is says that rows are affected, which I would think means it's working). Maybe I am not giving it an appropriate update command or something. Or maybe this can't be done. Any help would be GREATLY appreciated! Thanks in advance! BEGIN --DECLARE AND SET VARIABLES DECLARE @PrevASD DateTime, @PrevExecDt DateTime --PUT DESIRED RESULTS SET INTO A TEMP TABLE (STORED IN MEMORY) SELECT CR.IdStepCalc As IdStepCalc, CR.idCalc as IdCalc, CR.AppliedStartDate As ASD, CR.AppliedEndDate As AED, CR.ExecutedDate As ExecDt, CR.BasedOnStartDate As BOSD, CR.BasedOnEndDate As BOED, CR.ResultValue As ResultValue, CR.RerunReason As RerunReason, CR.Userid As UserID, CR.TaskName As TaskName, FR.TotalAdjusts As TotalAdjusts, CR.IdStepResult as IdStepResult, CASE WHEN FR.TotalAdjusts IS NULL THEN CR.ResultValue ELSE CR.ResultValue + FR.TotalAdjusts END As FinalResult, NULL AS ChangeColor INTO #CalcResultsColorChangeTable FROM IncentivePlans P INNER JOIN IncentivePlanSteps S ON P.idPlan = S.idPlan INNER JOIN IncentiveStepCalcs C ON S.idStep = C.idStep INNER JOIN IncentiveCalcResults CR ON C.idCalc = CR.idCalc LEFT JOIN (SELECT CR.IdStepCalc, SUM(CA.AdjustmentValue) As TotalAdjusts FROM IncentiveCalcResults CR INNER JOIN IncentiveCalcAdjustments CA ON CR.IdStepCalc=CA.IdStepCalc WHERE CA.AdjustmentValue IS NOT NULL GROUP BY CR.IdStepCalc) FR ON CR.IdStepCalc = FR.IdStepCalc --VALUES HARD-CODED FOR NOW, BUT WILL BE SENT IN AS PARAMETERS LATER WHERE P.idPlan = 1 AND S.idStep = 2 AND C.idCalc = 3 ORDER BY CR.AppliedStartDate DESC, CR.ExecutedDate DESC --DECLARE VARIABLES FOR CURSOR DECLARE @UpdateColorCursor Cursor, @IdStepCalc int, @IdCalc int, @ASD datetime, @AED datetime, @ExecDt datetime, @BOSD datetime, @BOED datetime, @ResultValue Decimal(18), @RerunReason VarChar(250), @UserID Char(12), @TaskName Char(20), @TotalAdjusts Decimal(18), @IdStepResult int, @FinalResult decimal(18), @ChangeColor Bit --CREATE CURSOR SET @UpdateColorCursor = CURSOR FOR SELECT IdStepCalc, IdCalc, ASD, AED, ExecDt, BOSD, BOED, ResultValue, RerunReason, UserID, TaskName, TotalAdjusts, IdStepResult, FinalResult, ChangeColor FROM #CalcResultsColorChangeTable --OPEN CURSOR OPEN @UpdateColorCursor --RETRIEVE FIRST ROW OF RECORD SET FETCH NEXT FROM @UpdateColorCursor INTO @IdStepCalc, @IdCalc, @ASD, @AED, @ExecDt, @BOSD, @BOED, @ResultValue, @RerunReason, @UserID, @TaskName, @TotalAdjusts, @IdStepResult, @FinalResult, @ChangeColor --FETCH NEXT RECORD WHILE @@FETCH_STATUS = 0 BEGIN --PRINT OUT FOR TESTING ONLY, WILL BE REMOVED LATER PRINT Cast(@ASD As SmallDateTime) PRINT Cast(@PrevASD As SmallDateTime) PRINT @ExecDt PRINT @PrevExecDt --DO STUFF WITH AN INDIVIDUAL ROW --If the ASD is the same as the previous, then check the ExecDt IF(Cast(@ASD As SmallDateTime) = Cast(@PrevASD As SmallDateTime)) BEGIN --If the ExecDt is old, we will gray it out IF(@ExecDt <= @PrevExecDt) BEGIN UPDATE #CalcResultsColorChangeTable SET @ChangeColor = 1 WHERE CURRENT OF @UpdateColorCursor END ELSE BEGIN UPDATE #CalcResultsColorChangeTable SET @ChangeColor = 0 WHERE CURRENT OF @UpdateColorCursor END END ELSE BEGIN UPDATE #CalcResultsColorChangeTable SET @ChangeColor = 0 WHERE CURRENT OF @UpdateColorCursor END --SET CURR DT VALUES AS PREVIOUS FOR THE NEXT ROW SET @PrevASD = @ASD SET @PrevExecDt = @ExecDt --MOVE THE CURSOR TO THE NEXT ROW FETCH NEXT FROM @UpdateColorCursor INTO @IdStepCalc, @IdCalc, @ASD, @AED, @ExecDt, @BOSD, @BOED, @ResultValue, @RerunReason, @UserID, @TaskName, @TotalAdjusts, @IdStepResult, @FinalResult, @ChangeColor END --CLOSE AND DEALLOCATE CURSOR CLOSE @UpdateColorCursor DEALLOCATE @UpdateColorCursor --DISPLAY RESULTS FOR TESTING, WILL BE REMOVED LATER SELECT * FROM #CalcResultsColorChangeTable --EXPLICITLY DROP TABLE TO RELEASE MEMORY RESOURCES DROP TABLE #CalcResultsColorChangeTableEND |
|
Cursor not updating my temp table