SQL Server Performance

while loop

Discussion in 'General Developer Questions' started by chaser12, Aug 4, 2005.

  1. chaser12 New Member

    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 instead cursor?

    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
  2. Madhivanan Moderator

    Post the table structure with some sample data and expected result would be helpful

    To see the duplicates in the clientname

    Select ClientName,count(*) from yourTable group by ClientName having count(*)>1


    Madhivanan

    Failing to plan is Planning to fail

Share This Page