Nested Cursors

Aug 24, 2007

What is the best way to nest cursors?

This code does not seem to be returning me all of the data.





Code Snippet
DECLARE element_Cursor CURSOR FOR
SELECT ElementTypeRecNo
FROM dbo.tblTemplateElementType
where TemplateRecno = @TemplateRecNo
OPEN element_cursor
FETCH NEXT FROM Element_Cursor into @ElementTypeRecno
--delete from tblElementCPO
WHILE @@FETCH_STATUS = 0
BEGIN
select @Count = count (*)
from tblProjTypeSet
where ProjRecno = @ProjRecNo
if @Count > 0
begin
select @ProjTypeRecno = ProjTypeRecno
from tblProjTypeSet
where ProjRecno = @ProjRecNo
select @Count = count (*)
FROM dbo.tblElementTypeDep
where TemplateRecno = @TemplateRecNo
and ProjTypeRecno = @ProjTypeRecNo
if @Count > 0
begin
DECLARE ElementTypeDep_Cursor CURSOR FOR
SELECT ElementTypeDepRecNo, PreElementTypeRecNo,
PostElementTypeRecNo, ElapsedTimeDueDates, ElapsedTimePlanDates,
Description
FROM tblElementTypeDep
WHERE (TemplateRecNo = @TemplateRecNo)
AND (ProjTypeRecNo = @ProjTypeRecno)
AND (PreElementTypeRecNo = @ElementTypeRecno)
OPEN ElementTypeDep_cursor
FETCH NEXT FROM ElementTypeDep_Cursor
into @ElementTypeDepRecno, @PreElementTypeRecNo,
@PostElementTypeRecno, @ElapsedTimeDueDates, @ElapsedTimePlanDates,
@Description
WHILE @@FETCH_STATUS = 0
BEGIN
select @PreElementRecNo = ElementRecno
from tblElementCPO
where ProjRecNo = @ProjRecNo
and IssueRecno = @IssueRecNo
and ElementTypeRecno = @PreElementTypeRecno
if @PreElementRecno is not null
begin
select @PostElementRecNo = ElementRecno
from tblElementCPO
where ProjRecNo = @ProjRecNo
and IssueRecno = @IssueRecNo
and ElementTypeRecno = @PostElementTypeRecno
if @PostElementRecno is not null
begin
select @Count = count (*)
from tblElementDepCPO
where ElementTypeDepRecno = @ElementTypeDepRecno
and PreElementRecNo = @PreElementRecNo
and PostElementRecno = @PostElementRecno
if @Count = 0
begin
INSERT INTO tblElementDepCPO
(ElementTypeDepRecNo, PreElementRecNo,
PostElementRecNo, ElapsedTimeDueDates,
ElapsedTimePlanDates, Description,
ChangeDate, ChangePerson)
VALUES (@ElementTypeDepRecno, @PreElementRecNo,
@PostElementRecno, @ElapsedTimeDueDates,
@ElapsedTimePlanDates, @Description,
GETDATE(), CURRENT_USER)
end

select @Count = count (*)
from tblElementAttemptCPO
where ElementRecNo = @PostElementRecNo

if @Count = 0
begin

select @Count = count (*)
from tblElementAttemptCPO
where ElementRecNo = @PostElementRecNo

if @Count = 0
begin
select @NextPlanDate = ProjectedCompletionDate,
@NextDueDate = RequiredCompletionDate
from tblElementAttemptCPO
where ElementRecno = @PreElementRecNo
end
else
begin
select @NextPlanDate = @StartDate
select @NextDueDate = @StartDate
end

select @NextPlanDate =
dbo.fncAddBusinessDays (@NextPlanDate, @ElapsedTimePlanDates)

select @NextDueDate =
dbo.fncAddBusinessDays (@NextDueDate, @ElapsedTimePlanDates)

insert into tblElementAttemptCPO (ElementRecno,
ProjectedCompletionDate, RequiredCompletionDate,
ProjectedStartDate, RequiredStartDate,
ActualStartDate, ActualCompletionDate, AttemptNum,
IsCompleted, IsStarted, ResponsibleRoleTypeRecno,
ChangeDate, ChangePerson)
values (@PostElementRecno,
@NextPlanDate, @NextDueDate,
'1/11/1900', '1/11/1900',
'1/11/1900', '1/11/1900', 0,
0, 0, 0,
GETDATE(), CURRENT_USER)
end
end
end
FETCH NEXT
FROM ElementTypeDep_Cursor
into @ElementTypeDepRecno, @PreElementTypeRecNo,
@PostElementTypeRecno, @ElapsedTimeDueDates, @ElapsedTimePlanDates,
@Description
END

CLOSE elementTypeDep_Cursor
DEALLOCATE elementTypeDep_Cursor
end
FETCH NEXT FROM element_Cursor into @ElementTypeRecno
END
CLOSE element_Cursor
DEALLOCATE element_Cursor
end

View 2 Replies


ADVERTISEMENT

Nested Cursors - Me Am Very Bad :(

Sep 26, 2005

Hi all,

I have a temp guy who is doing some work for us (seriously, I never wrote the query).

Now this is the scenario (hold tight). So we have a stored procedure as follows...

ALTER PROCEDURE createSegmentPoints AS

DECLARE @fLat INTEGER
DECLARE @fLon INTEGER
DECLARE @segId INTEGER

-- declare cursor to return the from lat and lon for all segments that do not have address point 109.
DECLARE c1 CURSOR FOR (SELECT From_Latitude, From_Longitude, id AS segment_id FROM Segments WHERE SegmentType != 109)
OPEN c1
FETCH NEXT FROM c1 INTO @fLat, @fLon, @segId
WHILE @@FETCH_STATUS = 0
BEGIN
    -- insert into table the segId, from lat, from lon and returned segment id from function.
    INSERT INTO test VALUES (@segId,@fLat,@fLon,dbo.points_test(@fLat,@fLon))
   
    FETCH NEXT FROM c1 INTO @fLat, @fLon, @segId
END

CLOSE c1
DEALLOCATE c1

As you can see here I am using a Cursor, which in turn calls a function
with each row that is processed in the recordset. The function that is
called is as follows...

ALTER FUNCTION points_test(@x INTEGER, @y INTEGER)
RETURNS INTEGER
AS
BEGIN
    -- function to find the closed segment point with address point 109 to the segment specified in procedure.
    DECLARE @tempDistance FLOAT(4)
    SET @tempDistance = 1000000
    DECLARE @id, @seg, lat, lon INTEGER
    DECLARE @distance, @xd, @yd FLOAT
    DECLARE c1 CURSOR FOR (SELECT from_latitude, from_longitude, id FROM segments WHERE segmenttype = 109)
    OPEN c1
    FETCH NEXT FROM c1 INTO @lat, @lon, @id
    WHILE @@FETCH_STATUS = 0
    BEGIN
        -- calucations to get distance.
        SET @xd = (@lat-@x)
        SET @yd = (@lon-@y)
        SET @distance = SQRT((@xd*@xd) + (@yd*@yd))

        -- test if you have shortest distance.
        IF (@distance < @tempDistance)
        BEGIN
            SET @tempDistance = @distance
            SET @seg = @id
        END
        FETCH NEXT FROM c1 INTO @lat,@lon, @id
    END
    CLOSE c1;
    DEALLOCATE c1;
RETURN @seg
END

(This function works out an equation to get the shortest distance from
two parameters passed to the function calculated with data from each
row returned within the cursor)
As you can see here, this function contains ANOTHER cursor!! Ouch. The
fact that their is an SQL query in a function is a killer, but having
another embedded cursor there is also a killer - this has virtually
killer the application.

So, how best is it for me to correct this. Should I turn the function
into a stored procudure? But even if I do this, the nested cursor still
remains. I was thinking maybe to have the SQRT equations within the
SELECT expression and then wrapped in a MIN() to maybe get the lowest
value.

Any ideas would be of great help.

Thanks

Tryst

View 2 Replies View Related

Nested Cursors

Jun 3, 2004

Morning everyone,

I have a sp that I've created that is to show me everyone table name and column name using nested cursors. However when I execute the procedure it doesn't show me the names, it just tells me the command completed successfully. Here is the code:

CREATE PROCEDURE uspSeeAllViews
AS
SET NOCOUNT ON
DECLARE @strMessageVARCHAR(100)
DECLARE @strColumnVARCHAR(100)
DECLARE @strViewVARCHAR(100)
DECLARE @strCommandVARCHAR(250)

DECLARE crsViews CURSOR FOR

SELECT
name AS strView
FROM
sysobjects
WHERE
type = 'U'

OPEN crsViews
FETCH NEXT FROM crsViews INTO @strView
WHILE @@FETCH_STATUS = 0 BEGIN

DECLARE crsColumns CURSOR FOR

SELECT
name AS strColumn
FROM
syscolumns
WHERE
name = @strView

OPEN crsColumns
FETCH NEXT FROM crsColumns INTO @strColumn
WHILE @@FETCH_STATUS = 0 BEGIN

PRINT @strView + ':' + @strColumn
FETCH NEXT FROM crsColumns INTO @strColumn
END

CLOSE crsColumns
DEALLOCATE crsColumns

FETCH NEXT FROM crsViews INTO @strView
END

CLOSE crsViews
DEALLOCATE crsViews

Thanks for looking, any ideas??

View 7 Replies View Related

Nested Cursors

Apr 4, 2008

I have a table like:

Converted_Mortgage_Number Activity_Date Activity_Time Sequence_Number, History_Text

100 2006-01-20 84936 1 Sent a reminder letter
105 2006-01-23 134502 1 Called, but no reply
100 2006 01-24 104532 1 Asked to call again later
100 2006-01-24 104532 2 when it is more convenient
100 2006-01-24 104532 3 and the person I require is available

I wish to concantenate the History_text fields together for records with the same Converted_Mortgage_Number, Activity_Date and Activity_Time so:

100 2006-10-24 104532 Asked to call again later when it is moreconvenient and the person I require is available.

To do this I amusing nested cursors so:


USE ar
GO
DECLARE @mortgage INT,
@date datetime,
@time INT,
@getMortgage CURSOR,
@notes varchar(MAX),
@notesComplete varchar(MAX),
@getDetail CURSOR

SET @getMortgage = CURSOR FOR
SELECT Converted_Mortgage_Number, Activity_Date, Activity_Time
FROM format_history
GROUP BY Converted_Mortgage_Number, Activity_Date, Activity_Time
ORDER BY Converted_Mortgage_Number ASC
OPEN @getMortgage
FETCH NEXT
FROM @getMortgage INTO @mortgage, @date, @time
WHILE @@FETCH_STATUS = 0
BEGIN
SET @notes = ''
SET @getDetail = CURSOR FOR
SELECT History_Text
FROM format_history
WHERE Converted_Mortgage_Number = @mortgage AND Activity_Date = @date AND Activity_Time = @time
ORDER BY Sequence_Number
OPEN @getDetail
SET @notesComplete = ''
FETCH NEXT FROM @getDetail INTO @notes
WHILE (@@FETCH_STATUS = 0)
BEGIN
@notesComplete = @notesComplete + @notes + ' '
FETCH NEXT FROM @getDetail INTO @notes
END
PRINT @notesComplete
CLOSE @getDetail
DEALLOCATE @getDetail
FETCH NEXT
FROM @getMortgage INTO @mortgage, @date, @time
END
CLOSE @getMortgage
DEALLOCATE @getMortgage
GO


But I am getting the error: 'Incorrect syntax near the keyword CLOSE, incorrect syntax near @notesComplete'.

Can anyone please see where I am going wrong?

View 4 Replies View Related

I Just Hate Nested Cursors.. Please Help.....

Jul 1, 2004

I am not getting any error message. It just hangs...

Any idea what I am missing....


******************

ALTER PROCEDURE TrigSendPreNewIMAlertP2
@REID int

AS

Declare @RRID int
Declare @ITID int

Declare @FS1 int
Declare @FS2 int

Declare crReqRec cursor local for
select RRID from RequestRecords where REID = @REID and RRSTatus = 'IA' and APID is not null
open crReqRec
fetch next from crReqRec into @RRID
set @FS1 = @@Fetch_Status
while @FS1 = 0


Begin

Declare crImpGrp cursor local for
select ITID from RequestRecords where RRID = @RRID

open crImpGrp
fetch next from crImgGrp into @ITID
set @FS2 = @@Fetch_Status
while @FS2 =0
Begin
EXEC TrigSendNewIMAlertP2 @ITID

FETCH next from crImpGrp into @ITID

end
FETCH next from crReqRec into @RRID
end
close crImpGrp
deallocate crImpGrp
close crReqRec
deallocate crReqRec

View 1 Replies View Related

Nested Cursors Problem - Help

Dec 11, 2000

All,
I have a question on a nested Cursor statement, I am having problem getting the @@Fetch_Status to work correctly...

I can get the first cursor to fetch and the the second cursor to fetch all of it rows but when it reaches the end. It should fetch the first cursors next row and @@fetch_Status should be 0.

Is there a way around this???

Thanks,
~Lee

Alter Procedure Summary As
Declare @DateID nvarchar(50)
Declare @ShortDate nvarchar(30)
Declare @SourceIPID nvarchar(50)
Declare @SourceIP nvarchar(30)
Declare @Packets int

Declare lead_cur CURSOR FOR
SELECT tbl_Date.DateID, tbl_Date.ShortDate FROM Tbl_Date WHERE NOT tbl_date.shortdate = convert(char(11),GetDate())
FOR READ ONLY

Declare lead_cur2 CURSOR FOR
SELECT SourceIPID, SourceIP FROM tbl_SrcIP
FOR READ ONLY

Open lead_cur
Open lead_cur2

FETCH NEXT FROM lead_cur INTO @DateID,@ShortDate

While @@FETCH_STATUS = 0
BEGIN
FETCH NEXT FROM lead_cur2 INTO @SourceIPID, @SourceIP
While @@FETCH_STATUS = 0
BEGIN

Set @Packets = (Select ISNULL(Sum(CAST(strNumPackets As Int)),0)
FROM tbl_All_SysDATA
Where DateID = @DateID and SourceIPID = @SourceIPID)
If @packets > 0
Insert tbl_SysIPSummary (DateID,IPID,Packets) VALUES (@DateID,@SourceIPID,@Packets)

FETCH NEXT FROM lead_cur2 INTO @SourceIPID, @SourceIP
END
FETCH NEXT FROM lead_cur INTO @DateID,@ShortDate
END


Close lead_cur
Close lead_cur2
DEALLOCATE lead_Cur
DEALLOCATE lead_Cur2
GO

View 1 Replies View Related

Avoiding Nested Cursors

Dec 4, 2007

I have a Master/Detail table setup - let's call the master "Account" and the detail "Amount". I also have a "black box" stored procedure (BlackBox_sp) which carries out a lot of complex processing.

What I need to do is, for each Account, I need to iterate thtough it's Amount records and call the black box each time. Once I've finished going through all the Amount records, I need to call the black box again once for the Account. This must be done with the Account & Amount rows in a specific order.

So I have something along the lines of





Code Block

DECLARE Total int

DECLARE Account_cur
OPEN Account_cur
FETCH NEXT FROM Account_cur
WHILE FETCH_STATUS = 0
BEGIN

SET Total = 0


DECLARE Amount_cur
OPEN Amount_cur
FETCH NEXT FROM Amount_cur
WHILE FETCH_STATUS = 0
BEGIN

SET Total = Total + Amount

EXEC BlackBox_sp (Amount)
END
CLOSE Amount_cur

EXEC BlackBox_sp (Total)

END
CLOSE Account_cur

Any tips on another approach would be appreciated given the contraints I have.

Greg.

View 1 Replies View Related

I Am Trying To Use Nested Cursors. But Is There A Way To Use More Than One @@FETCHSTATUS Variable ?

Apr 18, 2008

I need to use a nested cursor in SQL server 2005. I have never done this before. The problem here is that I need to use "@@FETCH_STATUS' twice.Once for my inner loop and once for my outer loop. If there is a way to use two different @@FETCH_STATUS variables at once, I would like to know how.
Here is my code. The inner loop is currently pseudo code and I just need to figure out how to code it.
DECLARE @mycur1 CURSORDECLARE @mycur2 CURSORDECLARE @InMarketId INTSET @InMarketId=575 -- Hard code for now. Just for testing.DECLARE @test VARCHAR(10)SET @mycur1 = CURSORSET FORSELECT SubDivisionId FROM SubDivision WHERE MarketId=@InMarketIdOPEN @mycur1FETCH NEXT FROM @mycur1 INTO @testWHILE @@FETCH_STATUS = 0 BEGIN
 PRINT @test--       Begin Inner Nested Loop--         FOR EACH SubDivisionId in SubDivisionSubMarket SDSM delete from SubMarket SM Where SDSM.SubMarketId=SM.SubMarketId
--       End of Inner nested Loop
  FETCH NEXT FROM @mycur1 INTO @test    ENDDEALLOCATE @mycur1------------------------------------------Here are my three tables I am scripting agains and a description of each.
SubDivision-----------SubDivisionIdMarketIdNameDescriptionCreatedBy
SubDivisionSubMarket--------------------SubDivisionIdSubMarketId
SubMarket--------------------SubMarketIdCodeDescriptionLastUpdateDate

View 2 Replies View Related

Transact SQL :: Nested Cursors To Update Table?

May 15, 2015

I have been wrestling with the code all day to accomplish the following: I need to update a table based on values from another table. So far, I have been able to do the below:

DECLARE @LookUpTerm VARCHAR(25)
, @SearchCol VARCHAR(255)
, @LogonIDToProcess VARCHAR(50)
, @Matched CHAR
, @Cycle INT = 1
IF OBJECT_ID('tempdb..#Glossary','U') IS NOT NULL DROP TABLE #Glossary
IF OBJECT_ID('tempdb..#Employees','U') IS NOT NULL DROP TABLE #Employees

[code]...

View 7 Replies View Related

Using Cursors

Jul 16, 2006

Hello,
Can anyone direct me a good article that is about why we should try avoiding using cursors and what are the alternatives?

View 1 Replies View Related

Using Cursors From Asp.net

Feb 16, 2008

can we call cursors from asp.net as we can call stored procedures from asp.net.
 

View 1 Replies View Related

Cursors

Jan 7, 2005

Hi,

I'm writting a stored procedure to insert new rows into a table, but I need to loop through an exsiting table and create a new record for every row in the old table. So I think what I'm needing to use is a cursor, but I've never worked with cursors before, and I just want to check that I have the right idea on what they are used for and if what I have so far looks ok.

This what I have so far, the StoreFees table only has 5 rows:


DECLARE @StoreFee as smallmoney
DECLARE @StoreLineID as int

DECLARE MyStoreFees CURSOR
FORWARD_ONLY
FOR
SELECT LineId, StoreFee FROM StoreFees ORDER BY StoreFee

OPEN MyStoreFees

FETCH NEXT FROM MyStoreFees
INTO @StoreLineId, @StoreFee

--Do my Inserts into other tables
INSERT INTO OtherTable (...,...,.., @StoreFee)
...
...
-- Done working with that row

CLOSE MyStoreFees

View 5 Replies View Related

Cursors

Feb 28, 2005

I am trying to pull the data via fetching rows into a variable.

Begin
Fetch Next Into @temp...
Select @MainVariable = @MainVariable + @temp < @temp doesn't refresh with next fetch)

Print @temp < this works fine and returns the value for each fetch

End


I can print @temp and the data returns fine, but when I try to cancantenate with the above select, it doesn't refresh @temp wit the column in the table.

Any ideas?

DotNetNow

View 5 Replies View Related

Cursors

Jun 19, 2001

I have two procedures. One works well, and the other has a small glitch I cannot figure out. I have placed >>>> at the place where the problem is occuring.

The first procedure, which is working great, is applying a stored procedure to many servers (remote procedure call), but is also polling the local server via a linked server connection. This way, all servers are polled equally.

The second procedure is actually using a SELECT statment to query a system table. This procedure works well on all servers except the local one. I get this error message:
Server: Msg 3910, Level 16, State 1, Line 1
Transaction context in use by another session.
[OLE/DB provider returned message: Unspecified error]

There seems to be a connection issue. Can someone help me work around this?

Thank you,

Neal


FIRST PROCEDURE (this one works perfectly):
truncate table dbidname
CREATE TABLE ##dbtemp ( dbname VarChar(50),
dbsize varchar (25),
dbownervarchar(50),
dbid smallint,
createdate datetime,
statusvarchar(75) )
declare@servernamevarchar(50)
declare dbupdate cursor
global
for
select servername from sqlservers where status = 'a'
open dbupdate
fetch next from dbupdate into @servername
while @@fetch_status = 0
begin
print @servername
EXEC sp_addlinkedserver @servername, 'SQL Server'
>>>INSERT INTO ##dbtemp
>>>exec (@servername + '.master..sp_helpdb')
alter table dbidname alter column sqlservers_id int null
insert into dbidname (dbsystemid, dbname)
select dbid, dbname from ##dbtemp
update dbidname set sqlservers_id = sqlservers.sqlservers_id from sqlservers where @servername = sqlservers.servername and
dbidname.sqlservers_id is null
update dbidname set whenupdate = getdate()
alter table dbidname alter column sqlservers_id int not null
exec sp_dropserver @servername
delete ##dbtemp
fetch next from dbupdate into @servername
end
close dbupdate
deallocate dbupdate
drop table ##dbtemp
exec spal_db_files_update


SECOND PROCEDURE (see >>>> to note problem area):
truncate table dbidname
CREATE TABLE ##dbtemp ( dbname VarChar(50),
dbsize varchar (25),
dbownervarchar(50),
dbid smallint,
createdate datetime,
statusvarchar(75) )
declare@servernamevarchar(50)
declare dbupdate cursor
global
for
select servername from sqlservers where status = 'a'
open dbupdate
fetch next from dbupdate into @servername
while @@fetch_status = 0
begin
print @servername
EXEC sp_addlinkedserver @servername, 'SQL Server'
>>>>INSERT INTO ##dbtemp
>>>>exec (@servername + '.master..sp_helpdb')
alter table dbidname alter column sqlservers_id int null
insert into dbidname (dbsystemid, dbname)
select dbid, dbname from ##dbtemp
update dbidname set sqlservers_id = sqlservers.sqlservers_id from sqlservers where @servername = sqlservers.servername and
dbidname.sqlservers_id is null
update dbidname set whenupdate = getdate()
alter table dbidname alter column sqlservers_id int not null
exec sp_dropserver @servername
delete ##dbtemp
fetch next from dbupdate into @servername
end
close dbupdate
deallocate dbupdate
drop table ##dbtemp

View 1 Replies View Related

Cursors

Nov 28, 2000

I am receiving this error when running my cursor:

Error Messages...
Server: Msg 16933, Level 16, State 1, Line 0
The cursor does not include the table being modified.
The statement has been terminated.
Server: Msg 16933, Level 16, State 1, Line 5 (this message repeats)...
The cursor does not include the table being modified.
The statement has been terminated.

query:

declare cursor_test CURSOR for
select emp_ssn, effective_date1 from temp_employee_benefit_load
open cursor_test
declare @ssn char(9), @process_date char(8)
fetch next from cursor_test into @ssn, @process_date
while (@@fetch_status=0)
update test_cursor
set ssn = @ssn, process_date = @process_date
where current of cursor_test
fetch next from cursor_test into @ssn, @process_date

close cursor_test
deallocate cursor_test


Any help is appreciated:

Thanks,

View 3 Replies View Related

Cursors?????????

Sep 7, 2000

I have stored procedure in that I am using a cursor to fetch the row by row.
cursor is fetching 75000 records so that the procedure is taking long time.
Is there any way to replace the cursor to fetch the records row by row.

He needed.

Ranch

View 1 Replies View Related

Cursors

Apr 14, 2000

Please help resolve a problem (and debate!).

We have an app using VB with Access as the front end to SQL. SQL statments were built and sent
directly to the server obtaining set-oriented results. Response was fair.

We have a new app developed by an "expert" using VB & Interdev that was to be ported for use on
the internet. The app was designed using the same logic to build the SQL statements, but it is also
using cursors to retrieve all data. RESPONSE IS TERRIBLE!
The designer says that to access SQL over the Web, cursor use is a MUST!
True? Not True?
If true, and I am stuck with an app using cursors, any basic suggestions on where to look to improve
response time?

Thanks for any input.
FJ

View 1 Replies View Related

How To Use Cursors

Apr 23, 1999

Hi,

I am using SQL Server 6.5 and I have a VB routine that updates a field in a tble so it can be used in a primary key. This is run after input of data using bcp. I have noticed on several posts re: using cursors to move through a recordset and was wondering if I could use this functionality to replace my VB routine. I checked the archives and found several messages but I am not sure where to start. My VB routine is:

I first would query the data to return all rows where the value of R08SegmentValue= Null. Then I move through the resultset changing the value of the field R08Segments.

***********************Start of VB Code****************
'Set initial value for sTReportHeaderIdx
With rs1
sTReportHeaderIdx = !ReportIdx & !HeaderIdx
sPReportHeaderIdx = sTReportHeaderIdx
End With

bCount = 1

'loop through rs and when streportheaderidx changes reset bCount to 1
'otherwise increment bcount by 1 and write to field R08SegmentValue
Do While rs1.EOF = False And blnContinue = True
DoEvents
Do While sPReportHeaderIdx = sTReportHeaderIdx
With rs1
.Edit
!R08SegmentValue = bCount
.Update
bCount = bCount + 1
sPReportHeaderIdx = sTReportHeaderIdx
.MoveNext
If Not rs1.EOF Then sTReportHeaderIdx = !ReportIdx & !HeaderIdx
End With
Loop
sPReportHeaderIdx = sTReportHeaderIdx
bCount = 1
Loop

In the end my data should look like

ReportIdx HeaderIdx R08SegmentValue
1 1 1
1 1 2
2 1 1
2 1 2
2 1 3
3 1 1
3 1 2
3 1 3
1 2 1
1 2 2
1 2 3
etc..


Can this be done via an sp? How would I go about writing something like this?

Thanks in advance...any assistance you could provide would be appreciated!

Kevin

View 1 Replies View Related

Using Cursors

Aug 6, 2002

I hope someone can help me with this, as BOL didn't get me the answer I am looking for.

I want to write an update step that will transform the following:

1 abc
1 def
2 lmn
3 rst
4 tuv
4 xyz
4 jkl
5 pqr

into this:

1 abc,def
2 lmn
3 rst
4 tuv,xyz,jkl
5 pqr

In other words, I want to concatenate multiple instances into 1 record per row, but I am not familiar with working with cursors or loops.

Thanks in advance!
bv

View 1 Replies View Related

Cursors - Use Them Or Not???

Nov 16, 1998

According go textbooks and T-SQL developers experience - using cursors is not recommended, more over they say: avoid cursors where it's possible.

Could someone please recommend any other way to go through the recordset(resultset) forward and backward to perform some search or calculations, if there are a specific requirement for not using front-end tools such as VB or MS Access(please don't ask why), other words - all the work must be performed in T-SQL stored procedure.

Thanks

View 3 Replies View Related

Cursors

Feb 24, 2008

Hi
I defined a cursor and executed it...but now i exactly forgot what select statement i had run in the cursor..(forgot the columns that i am extracting )

how do I view the contents of the cursor ?

View 1 Replies View Related

T-SQL Cursors?

Aug 25, 2004

I have a stored proc that merges records from an undeduped table to a deduped table. It is running really slowly. Merging 70 million records against a deduped 70 million is taking 115 hours and counting on decent hardware. Very slow.

I suspect there is significant room for optimization here. Some of my ideas:

- Write/update back to the cursor rather than executing separate UPDATE statements.
- Try a dynamic cursor instead of a READ ONLY cursor.
- Dump new elements to a separate table or a file and then do a single large INSERT.

Anyone else think any of these ideas will work? Can anyone think of something better?

BTW, I've tried to replaced the procedural cursor code with set based UPDATES/INSERTS but the short version of the story is that that route just didn't pan out. I know that is very common optimization advice.

I've made minor simplifications to the code:
- Took out code to handle last line
- Took out progress logging code
- Removed some DECLARE statements. These are needed to run but it should be obvious what they were supposed to be.

View 10 Replies View Related

Cursors

Aug 27, 2004

Trying to understand cursors a little better, found this in one of the dbs I inherited. Just trying ot figure out why they put it there cause no one else knows anything about it.

DECLARE [TM #] CURSOR
FOR SELECT * FROM [2004 TERMS];

View 10 Replies View Related

Cursors

Jan 14, 2005

nevermind forget I asked thanks anyways

View 11 Replies View Related

Cursors Are Bad

Feb 10, 2005

These guys I work with have some sql scripts they run over night and they bog down the server and the machine will be gummed up in the morning etc..

Well, I finally looked at this processing and the culprit is cursors. And cursors within cursors. I would like to just get some opinions about what would be more processor efficient so I can send my boss a link to this thread.

Using a cursors to pull records and update them.

vs

Create script using a scripting language that pulls the records through ADO, loops through them and performs updates as necessary using update statements and the like.

Be nice. I have to work with these guys.

View 9 Replies View Related

Cursors

Jan 24, 2006

In what situations would you use a cursor over a t-SQL update/select statement

View 3 Replies View Related

Cursors....How To Get Away From Using?

Jun 26, 2007

Hey guys

I have heard cursors are not the way to go. But I am wondering if/how to get out of a situation that I am using a cursor in...in order to make my stored proc run more effieciently.

I am quite novice in my abilities and I am completely stumped on how to get around using them.

As far as INSERTs go, I think I can work around that, but how would I write UPDATE statements for all lines of a table to say pull a key from another table to reference them together?


I usually make my SELECT statement in the cursor, then update against the criteria from the SELECT statement. Now this is quite a slow process when I am updating 100K records.

Any help or pointers or a link to a good tutorial would be woderful.

Thanks
tibor

View 6 Replies View Related

Cursors

Jan 23, 2004

declare BadRecords cursor dynamic for select lngZipCodeID
from ZipCodes where lngZipCode=@ZipCode and lngZipCodeID<>@NewZipID

/* this is the syntax in Sybase
open BadRecords;
BadRecordsLoop: loop
fetch next BadRecords into BadID;
if sqlstate<>0 then
leave BadRecordsLoop
end if
*/

I have to convert it to sql , I m just checking whether my syntax is correct or not

open BadRecords
while @@fetch_status = 0
begin
set @BadId = fetch next BadRecords
if @@Error = 0
end


I have doubt in my syntax. Can you help me out in this?


Thx

View 4 Replies View Related

If Not CURSORS ??

Feb 11, 2004

I have a requirement to check a value which MUST be unique forever even if it is removed and readded.I have created a seperate table to maintain this value.

Without using a cursor how would I be able to append a duplicate base value (i.e. smith.j@here.now) with the next sequential value (i.e. smith.j02@here.now)

Any takers?

Oh ya, These values are not manually entered but populated through a DTS script. The existing values are repopulated from historic tables and new entries are added automatically. Initially the values would be populated without a number but a number needs to be generated on duplicates.

View 8 Replies View Related

Can Anyone Help Me On Cursors

Apr 8, 2008

Could not complete cursor operation because the table schema changed after the cursor was declared.

View 5 Replies View Related

Cursors Or ?

Sep 28, 2006

I have a Stored Procedure that show RentArrears for a tenant only
eg Execute RentArrears '88' where 88 is the tenantID
Now I want to execute it for other tenants, I want think of using Cursors to loop thru tenent table , but everybody is frowning at it, what other method can I use to loop thru tenant so as to use my stored procedure
Thanks



If it is that easy, everybody will be doing it

View 5 Replies View Related

Cursors

Jul 24, 2007

Can someone tell me what is wrong with this code?
I just want to get the account_number field from the accounts table and put it in the results table. Let me clarify, there are over 500 rows in the accounts table and I want to loop through those while setting the accounts.account_number = results.account_number.

declare @account nvarchar

DECLARE Accounts_Cursor CURSOR FOR
SELECT account_number FROM accounts
OPEN accounts_Cursor
FETCH NEXT FROM accounts_Cursor into @account
WHILE @@FETCH_STATUS = 0
BEGIN
update results
set account_number = @account
FETCH NEXT FROM accounts_Cursor into @account
END

CLOSE accounts_Cursor
DEALLOCATE accounts_Cursor

View 10 Replies View Related

CURSORS!!!!!!!!!!!!!!!!!!!!!!

Feb 3, 2008

declare @sal char(50)

DECLARE Employee_Cursor1 CURSOR static for
SELECT salary
FROM test2


OPEN Employee_Cursor1

FETCH FROM Employee_Cursor1 into @sal


WHILE @@FETCH_STATUS = 0

BEGIN
FETCH NEXT FROM Employee_Cursor1 into @sal
PRINT 'this is NOT the desired output which i wanted from so long :'+@sal

END

CLOSE Employee_Cursor1
DEALLOCATE Employee_Cursor1

Rahul

View 5 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved