here my code it removes all the duplicate contactname and some of the clientnames, I should have 700 records I have 199 shows. I would like to get rid of cursors, they are very slow. How can I use while loop on this code?

DECLARE @TestFolderPath VarChar(255)

SET @TestFolderPath = 'SQL1_Contacts'

DECLARE @Contactname varchar(50),
@contmethodvalue varchar(100),
@contmethodtype VarChar(15),
@Phone VarChar(50),
@Email VarChar(50),
@billingaddress1 VarChar(50),
@billingCity VarChar(50),
@billingState VarChar(50),
@billingZip VarChar(50),
@groupid varchar(10),
@clientname varchar(50),
@status varchar(10)


DECLARE Inserted_Cursor CURSOR FORWARD_ONLY READ_ONLY FOR

select c.clientname,a.contactname,(select top 1 comethodvalue from localtest.dbo.contactmethods where contactid = [a].[contactid] and comethodtype = 'Phone' ORDER BY comethodvalue) AS Phone,(select top 1 comethodvalue from localtest.dbo.contactmethods where contactid = [a].[contactid] and comethodtype = 'Email' ORDER BY comethodvalue) AS Email, c.billingaddress1, c.billingcity, c.billingstate, c.billingzip

From localtest.dbo.contacts as a , localtest.dbo.clients as c
where a.clientid = c.clientid and c.groupid = 'RERE'
AND a.stat IS NULL OR a.stat <> 'inactive'
and a.contactname <> ''

OPEN Inserted_Cursor

FETCH NEXT FROM Inserted_Cursor INTO @clientname,
@contactname,
@Phone,
@Email,
@billingaddress1,
@billingCity,
@billingState,
@billingZip

WHILE @@FETCH_STATUS = 0 -- see if there are any more rows--
BEGIN

EXEC master.dbo.xp_mycontacts @FolderPath = @TestFolderPath,
@matchFileAs = @contactname,
@firstname = @clientname,
@Phone = @Phone,
@Email = @Email,
@Street = @billingaddress1,
@City = @billingCity,
@State = @billingState,
@Zip = @billingZip

FETCH NEXT FROM Inserted_Cursor INTO @contactname,
@clientname,
@Phone,
@Email,
@billingaddress1,
@billingCity,
@billingstate,
@billingZip
END

CLOSE Inserted_Cursor; DEALLOCATE Inserted_Cursor

Cursors are not slow if you do them right. No matter what you do you have to do a minimum of one table scan on contactmethods.

If your slow its probably because your process EXEC master.dbo.xp_mycontacts is not using any indexes. Please post that code....

Cursors are always slow, relative to a set based solution, indexes or not and whether you do them "right" (whatever that means) or not.

Just glancing at the OP's code, my first question would be to ask what that master.dbo.xp_mycontacts procedure was...

Jeff Mason
Custom Apps, Inc.
www.custom-apps.com

I agree that cursors are usually slower, but often the control that they give is often worth it. You can do things that are impossible with set operations.

As to making your proc more effiecent, without knowing what the xp_mycontacts proc does, i do not think you are going to gain much. You can try to make the base query a little more efficent. Try something like:

select c.clientname
       ,a.contactname
       ,min(p.comethodvalue) as Phone
       ,min(e.comethodvalue) as Email
       ,c.billingaddress1
       ,c.billingcity
       ,c.billingstate
       ,c.billingzip
  from localtest.dbo.contacts as a
    inner join localtest.dbo.clients as c on c.clientid = a.clientid
                                         and c.groupid = 'RERE'
    left join localtest.dbo.contactmethods p on p.clientid = a.clientid
                                         and p.comethodtype = 'Phone'
    left join localtest.dbo.contactmethods e on e.clientid = a.clientid
                                         and p.comethodtype = 'Email'
  where (a.stat IS NULL OR a.stat <> 'inactive')
    and a.contactname <> ''
  group by c.clientname
       ,a.contactname

If xp_mycontacts is doing inserts to a table, use the SQL statement as the driver for an INSERT statement and eliminate cursors all together.

Cursors are always slower than the equivalent set-based operation.

I agree that there some things you can only do with cursors. I simply suggest that you make sure that your problem is really one of those that can't be solved any other way.

Many times people user a cursor because it's easier to think of a solution that way, one row at a time, rather than considering a solution operating on a set of rows, all at once.

Jeff Mason
Custom Apps, Inc.
www.custom-apps.com

I think the real question is what your comparing the cursor to if they are slow relatively. Cursors go through row by row, something SQL was not designed to do. If you compare them to other things that do thing row by row and not to a whole table they are not slow assuming you don't write them in ways where they have to do lookups on indexed data. Using a while statment is not going to get you faster results than a cursor if what you need to do requires going through the rows one at a time. If you don't need to hash through data a row at a time don't use a cursor and it will be faster. Thats what I am trying to say. I frequently see people blaming cursors for their bad coding practices or because the task they are trying to accomplish requires hashing through records row by row. If you wrote dot net code that had to go through data row by row it was be even slower than a cursor. It's all relative. Having a task requiring that you go through the data row by row is always slower than doing something like a simple update to the whole table.