while

Question

while

chaser

SSC Rookie

Points: 36
More actions
August 4, 2005 at 10:04 am

#165543

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 loops instead cursors
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

Viewing 8 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 1

Can you post the table(s)'(s) definition and also sample data and the expected results of the proc. I don't have enough info to help you at the moment.

chaser SSC Rookie Points: 36 More actions · Answer 2

how can I get rid of cursors and just go by while loop

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 3

That's why I want to anser... but you won't even need the loops once I can convert to a set based version.

chaser SSC Rookie Points: 36 More actions · Answer 4

chaser

SSC Rookie

Points: 36

August 4, 2005 at 11:51 am

#579763

okay how can I do that? so I can send you...

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 5

for the table script : Click on the table(s) in EM, right click/copy.

If you want to send sample data which would be appreciated :

Help us help you

chaser SSC Rookie Points: 36 More actions · Answer 6

result set

clientname contactname phone billingaddress1 billingcity billingstate billingzip

xx Enterprises, LLC Jaon smith (555)555-1528 445 Marks St. somewhere NV 55555

sample data for contactmethods would be

2308 1 Phone (555)555-1820 8/2/2004 9:53:00 AM jhkhj 8/2/2004 9:53:00 AM dfgdfg

for clients

8909 454545 rere Country Club Client joan smith ddd2403$&amp=ddsd Some Enterprises, Inc. The Golf Club,Ltd. (555)555-6644 x226 (555)555-5899 P.O. Box 350 somewhere NV 55551-0350 P.O. Box 350 somewhrer NV 55551-0350 1/1/1999 RERE 6/11/1999 5/17/2005 1:36:00 PM soemdb

for contacts

1 10002 joan smith 8/2/2004 9:49:00 AM dfdfd 8/2/2004 9:49:00 AM fdfd

CREATE TABLE [Clients] (

[ClientID] [int] NOT NULL ,

[ClientNumber] [varchar] (15) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,

[GroupID] [varchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,

[Industry] [varchar] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,

[ClientType] [varchar] (15) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,

[RelatedClientNumber] [varchar] (15) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,

[PrimaryContact] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,

[AccountNumber] [varchar] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,

[ClientName] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,

[DBA] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,

[Phone1] [varchar] (15) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,

[Phone2] [varchar] (15) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,

[Phone3] [varchar] (15) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,

[P1Ext] [varchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,

[p2Ext] [varchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,

[p3Ext] [varchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,

[Fax] [varchar] (15) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,

[ShippingAddress1] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,

[ShippingAddress2] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,

[ShippingCity] [varchar] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,

[ShippingState] [char] (2) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,

[ShippingZip] [varchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,

[BillingAddress1] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,

[BillingAddress2] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,

[BillingCity] [varchar] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,

[BillingState] [char] (2) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,

[BillingZip] [varchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,

[StartDate] [smalldatetime] NULL ,

[TerminationDate] [smalldatetime] NULL ,

[Products] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,

[CreateTmsp] [smalldatetime] NULL ,

[CreateUser] [varchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,

[UpdateTmsp] [smalldatetime] NULL ,

[UpdateUser] [varchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL

) ON [PRIMARY]

GO

CREATE TABLE [Contacts] (

[ContactID] [int] NOT NULL ,

[ClientID] [int] NULL ,

[ContactName] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,

[ContactTitle] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,

[ContactType] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,

[UserName] [varchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,

[Greeting] [varchar] (25) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,

[PrimaryContact] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,

[Status] [varchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,

[CreateTmsp] [smalldatetime] NULL ,

[CreateUser] [varchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,

[UpdateTmsp] [smalldatetime] NULL ,

[UpdateUser] [varchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,

[msrepl_tran_version] [uniqueidentifier] NOT NULL

) ON [PRIMARY]

GO

CREATE TABLE [ContactMethods] (

[ContMethodID] [int] NOT NULL ,

[ContactID] [int] NOT NULL ,

[ContMethodType] [varchar] (15) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,

[ContMethodValue] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,

[CreateTmsp] [smalldatetime] NOT NULL ,

[CreateUser] [varchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,

[UpdateTmsp] [smalldatetime] NOT NULL ,

[UpdateUser] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,

[msrepl_tran_version] [uniqueidentifier] NOT NULL

) ON [PRIMARY]

GO

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 7

I just realized that you were calling an extended proc... I thaught it was a normal sp and that the whole work could somehow be encapsulated in a single operation.

Normally a while would be something like this :

Declare @ID as int

Declare @LastID as int

set @ID = NULL

Select TOP 1 @ID = id, @Adress = Adress... from YourQuery ORDER BY ID

WHILE @ID IS NOT NULL

begin

exec SP...

Select @ID = NULL, @LastID = @ID

Select TOP 1 @ID = id, @Adress = Adress... from YourQuery

where id > @LastID

ORDER BY ID

end

Sorry for the trouble and I hope I didn't forget anything. I haven't used this technic in over a year.