how do we deal with cursor for two columns

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

how do we deal with cursor for two columns

Author

Topic

rtutus
Aged Yak Warrior

522 Posts

Posted - 2007-01-30 : 23:15:37

to decalare a cursor for one column i do this:

declare @param as varchar(8000)
declare crsMyTblParams cursor for
select Loginname from #serverLoginDetails
open crsMyTblParams
fetch next from crsMyTblParams into @param

How about if i need a cursor for 2 columns: Loginname and MemberOf that i will put in two variables: @param1 and param2

This doesn t work for me:

declare crsMyTblParams cursor for
select Loginname, MemberOf from #serverLoginDetail
...
Thank you

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-01-30 : 23:22:52

[code]
declare @Loginname varchar(100),
@MemberOf varchar(100)

declare crsMyTblParams cursor for
select Loginname, MemberOf from #serverLoginDetails

open crsMyTblParams

fetch next from crsMyTblParams into @Loginname, @MemberOf
[/code]

rtutus
Aged Yak Warrior

522 Posts

Posted - 2007-01-31 : 11:04:43

Thank you khtan.

TonyTheDBA
Posting Yak Master

121 Posts

Posted - 2007-02-02 : 06:25:30

Or if you wanted to avoid the use of cursors


DECLARE @RowCount Integer
DECLARE @LoginName Varchar ( 1000 )
DECLARE @MemberOf  Varchar (  100 )

CREATE TABLE #TblParams ( LoginName Varchar ( 1000 )
                        , MemberOf  Varchar (  100 ) )

INSERT INTO #TblParams
  SELECT Loginname , MemberOf FROM #serverLoginDetails

SELECT TOP 1 @LoginName = loginName
           , @MemberOf  = MemberOf
  FROM #TblParams

SET @RowCount = @@ROWCOUNT

WHILE @RowCount <> 0
  BEGIN

    -- DO Your Cursor Based logic here


    -- And then you need to follow up with this

    -- Delete the row just processed from the Temporary TABLE
    -- Otherwise we will process the same row in a continual loop . . .  Bad!

    DELETE FROM #TblParams 
     WHERE LoginName = @LoginName
       AND MemberOf  = @MemberOf

    -- And Finally Get the next Row to Process

    SELECT TOP 1 @LoginName = loginName
               , @MemberOf  = MemberOf
      FROM #TblParams

    SET @RowCount = @@ROWCOUNT

  END

DROP TABLE #TblParams

I realise that this seems a lot more complex, but you quite often see experienced people posting here that Cursors are evil and should be avoided, using a Set Based Approach is much faster,Less resource intensive . . . . but rarely do you see a post that simply illustrates how to replace Cursor based logic with a While loop, which is my intention here.

--
Regards
Tony The DBA

rtutus
Aged Yak Warrior

522 Posts

Posted - 2007-02-02 : 08:48:59

cool mr dba :)

Jeff Moden
Aged Yak Warrior

652 Posts

Posted - 2007-02-05 : 00:58:53

Replacing cursor logic with a Temp Table and a WHILE loop isn't much better than the cursor that it replaces either in performance or resources used. It does cut down on certain locking but so does a "firehose" cursor. I mean, what do folks think a cursor actually is???

The best thing to do, if possible (it usually is with some very rare exceptions), is to replace the cursor with good setbased logic. Be careful when "borrowing code" to replace a cursor... there's a lot of things that look setbased that are actually worse than a cursor!

--Jeff Moden

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources