How to reflect base table changes in cursor

Transact-SQL

itsAshish


I have a small question -

I have a cursor which is running on the table and i am deleting some rows from the same table during the cursor loop but it didnt reflecting it. Is there is any other option i need to sepcify during declare.

declare localshowcursor cursor for
SELECT video_release, programname, network, date_encoded, time_encoded, COUNT(*) AS Number
FROM SigmaStageTemp s
Where program_type = ¡®1¡¯
Group by video_release, programname, network, date_encoded, time_encoded
Having count(*) >= 10
Order by video_release, programname, network, date_encoded, time_encoded

Please help

Top

Re: How to reflect base table changes in cursor

Arnie Rowland


Ashish,

Most likely, you don't need to be using a CURSOR.

Please post the code that executes for the CURSOR position, and perhaps we can help you do the same task in a SET based operation -which will be faster and less disruptive than using a CURSOR.






Top

Re: How to reflect base table changes in cursor

itsAshish

Below is my complete code. It select the range of records from the base table and insert into a different table and then deletes those records from the base table but when i debuged the cursor i am still getting the value of time_encoede which should be deleted.
declare @pgname nvarchar(20)
declare @videorelease nvarchar(40)
declare @dateaired nvarchar(15)
declare @dateencoded nvarchar(15)
declare @timeencoded nvarchar(15)
declare @network nvarchar(12)
declare @count int

declare localshowcursor cursor for
SELECT video_release, programname, network, date_encoded, time_encoded, COUNT(*) AS Number
FROM SigmaStageTemp s
Where program_type = ¡®1¡¯
Group by video_release, programname, network, date_encoded, time_encoded
Having count(*) >= 10
Order by video_release, programname, network, date_encoded, time_encoded

OPEN localshowcursor

FETCH NEXT FROM localshowcursor INTO @videorelease, @pgname, @network, @dateencoded, @timeencoded, @count

WHILE @@FETCH_STATUS = 0
BEGIN

Insert into sigmtemp(Video_release, country, market_rank, Designated_mrkt_area, station, network, date_aired, day_of_week, day_part, half_hour_aired, programname, program_type, start_time, end_time, length_aired, date_encoded, time_encoded, rel_type, VR_CODE1, VR_CODE2, VR_CODE3, VR_CODE4, DMA_CODE, sid_code, station_code)
(Select top(1)
Video_release, country, market_rank, Designated_mrkt_area, station, network, date_aired, day_of_week, day_part, half_hour_aired, programname, program_type, start_time, end_time, length_aired, date_encoded, time_encoded, rel_type, VR_CODE1, VR_CODE2, VR_CODE3, VR_CODE4, DMA_CODE, sid_code, station_code
from sigmastagetemp s1
where s1.video_release = @videorelease and
s1.programname = @pgname and
s1.network = @network and
s1.date_encoded = @dateencoded and
convert(int,s1.time_encoded) between convert(int,@timeencoded) -2 and
convert(int,@timeencoded) +2
Order by video_release, programname, network, date_encoded, time_encoded )


Delete
from sigmastagetemp
where video_release = @videorelease and
programname = @pgname and
network = @network and
date_encoded = @dateencoded and
convert(int,time_encoded) between convert(int,@timeencoded) -2 and
convert(int,@timeencoded) +2

FETCH NEXT FROM localshowcursor INTO @videorelease, @pgname, @network, @dateencoded, @timeencoded, @count
END

CLOSE localshowcursor
DEALLOCATE localshowcursor



Top

Re: How to reflect base table changes in cursor

Manivannan.D.Sekaran

You can't achive it on the cursor.

When you use the GROUP BY, all the result will be stored in the temp table and the temp table will be served for your cursor.

The only communication with your table is on the DECLARE & OPEN cursor. After that it will be acted independetly....

The Following query proves this,

Code Snippet

Create table TESTCUR

(

Num Int,

Chr Char

)

Insert Into TESTCUR VALUES(1,'A')

Insert Into TESTCUR VALUES(1,'A')

Insert Into TESTCUR VALUES(1,'A')

Insert Into TESTCUR VALUES(1,'B')

Insert Into TESTCUR VALUES(1,'B')

Insert Into TESTCUR VALUES(1,'B')

Insert Into TESTCUR VALUES(1,'C')

Insert Into TESTCUR VALUES(1,'C')

Insert Into TESTCUR VALUES(1,'D')

Go

Declare TESTCURFUN Cursor DYNAMIC

For Select Count(NUM) C,Chr From TESTCUR Group BY Chr;

Declare @I as int, @N Char;

Open TESTCURFUN

Fetch TESTCURFUN INTO @I,@N

--Drop the Table

Drop Table TESTCUR

While @@FETCH_STATUS = 0

Begin

Select @I,@N

Fetch TESTCURFUN INTO @I,@N

End

Close TESTCURFUN

DEALLOCATE TESTCURFUN

--Try the same logic without group by you might get error.





Top