Cursors

[VBXL]

Can every SQL problem be solved without using cursors.
If not, what type of problems cannot be solved.

Cheers

VBXL

 

[jymm]

most situations should be able to be solved without cursors. Think of a huge phone book (pick a BIG city near you) and having to go from the start to the finish to look at each phone record (ok, so that is extreem, you would usually qualify the records a bit - but it get the point across). That is what a cursor will do. Much better/faster to go to the correct record to update (using an index) or using hashing to go to the 'S' section to start looking for 'Smith'.

One of the only times that I have had to use a cursor I had a small amount of data that needed to be updated and there were constraints with another table involved (too complicated to type in here).

this help or make sense?

 

[SQLSister]

We don't allow the use of cursors here, although if a programmer proved he had tried other possibilities and they didn't work for the situation, we'd let him use them. Have never run into a problem that required them, but I feel certain there must be some.

 

[redgate]

Hi All

I have heard so much about not using cursors but sometimes cant find away around them. To make things worse I use temporary tables as well. Here is a procedure I wrote to return data for a pyramid reporting structue for 'n' levels. If some one could advise a better way to do this then I would appreciate it.

tblMLMRel contains just 2 columnms which are indexed.

Stored procedure.

@STRAID int,
@CNTLVL int

AS


SET NOCOUNT ON

CREATE TABLE #TMP1
(
LVL int Not Null,
AID int Not Null,
BID int Not Null
)


Declare @LVL int

SET @LVL = 1

INSERT #TMP1
SELECT LVL = LVL ,
AID ,
BID
FROM (SELECT AID = AID,
BID = BID,
LVL = @LVL
FROM ( Select MLMAID AS AID,
MLMBID AS BID
From tblMLMRel
Where MLMAID = @STRAID ) tmpSelect
) tmpResult

Set @LVL = @LVL + 1

While (@LVL <= @CNTLVL)

Begin

Declare Cursor1 CURSOR For

Select BID from #TMP1 Where LVL = (@LVL -1)

Open Cursor1
FETCH NEXT FROM Cursor1 INTO @STRAID
WHILE (@@FETCH_STATUS=0)
BEGIN

INSERT #TMP1

SELECT LVL = LVL ,
AID ,
BID
FROM (SELECT AID = AID,
BID = BID,
LVL = @LVL
FROM (Select MLMAID AS AID,
MLMBID AS BID
From tblMLMRel Where MLMAID = @STRAID ) tmpSelect
) tmpResult
FETCH NEXT FROM Cursor1 INTO @STRAID
END
Close Cursor1

DEALLOCATE Cursor1

Set @LVL = @LVL + 1

End

Select #TMP1.LVL,
#TMP1.AID,
#TMP1.BID,
From #TMP1


SET NOCOUNT OFF

 

[tlbroadbent]

I 've converted many procedures using cursors to handle relational sets. There have been some that I can't convert, at least not yet. Sometimes, a cursor solution is just easier to implement than a non-cursor routine. For example, many of our maintenaqnce routines use cursors to get a list of databases for backups or tables for reindexing. We could create procedures without cursors but I'm not sure it's worth the effort becaause these are low overhead read-only cursors. If you want to get the best answer for your question read faq183-874.

Terry L. Broadbent - DBA
SQL Server Page:

http://tlbroadbent.home.attbi.com/sql/sql_articles.htm