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
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.
|||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
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.
sql