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.

Our new SQL Server Forums are live! Come on over! We've restricted the ability to create new threads on these forums.

SQL Server Forums
Profile | Active Topics | Members | Search | Forum FAQ
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2000 Forums
 Transact-SQL (2000)
 how do we deal with cursor for two columns
 Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

rtutus
Aged Yak Warrior

522 Posts

Posted - 01/30/2007 :  23:15:37  Show Profile  Reply with Quote
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)

Singapore
17689 Posts

Posted - 01/30/2007 :  23:22:52  Show Profile  Reply with Quote

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



KH

Go to Top of Page

rtutus
Aged Yak Warrior

522 Posts

Posted - 01/31/2007 :  11:04:43  Show Profile  Reply with Quote
Thank you khtan.
Go to Top of Page

TonyTheDBA
Posting Yak Master

United Kingdom
121 Posts

Posted - 02/02/2007 :  06:25:30  Show Profile  Reply with Quote
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
Go to Top of Page

rtutus
Aged Yak Warrior

522 Posts

Posted - 02/02/2007 :  08:48:59  Show Profile  Reply with Quote
cool mr dba :)
Go to Top of Page

Jeff Moden
Aged Yak Warrior

USA
652 Posts

Posted - 02/05/2007 :  00:58:53  Show Profile  Reply with Quote
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

Edited by - Jeff Moden on 02/05/2007 00:59:12
Go to Top of Page
  Previous Topic Topic Next Topic  
 Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2019 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.02 seconds. Powered By: Snitz Forums 2000