loop through records - SQL Server Forums

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 2005 Forums

Transact-SQL (2005)

loop through records

Author

Topic

esthera
Master Smack Fu Yak Hacker

1410 Posts

Posted - 2007-03-08 : 03:10:30

how can i loop through records and call a sp for each record with teh values

so

select * from customers where status=0
loop through recordset
exec spsend @customerid=customerid where the values come from the select

mahesh_bote
Constraint Violating Yak Guru

298 Posts

Posted - 2007-03-08 : 03:17:05

quote:
Originally posted by esthera

how can i loop through records and call a sp for each record with teh values

so

select * from customers where status=0
loop through recordset
exec spsend @customerid=customerid where the values come from the select

what exactly u r doing? plz, explain in detail.

Mahesh

esthera
Master Smack Fu Yak Hacker

1410 Posts

Posted - 2007-03-08 : 03:19:33

i want to loop through the recordset and call another stored procedure (which actually does changes to each record)

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-03-08 : 03:31:01

You will required a while loop or cursor to do this. Note that if you have a large number of records in customers, the performance may be bad.

The best way of-couse is to change your stored procedure and do it there.

esthera
Master Smack Fu Yak Hacker

1410 Posts

Posted - 2007-03-08 : 03:32:15

how do i do this?

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-03-08 : 03:35:31

quote:
Originally posted by esthera

how do i do this?

which method ? Best, Bad, Worst ?

esthera
Master Smack Fu Yak Hacker

1410 Posts

Posted - 2007-03-08 : 03:41:49

any preferably best :)
i just need to know how to loop through a record and then I can apply to my sp as well

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-03-08 : 03:47:00

quote:
The best way of-couse is to change your stored procedure and do it there.

Don't LOOP ! Change your stored procedure !

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-03-08 : 03:50:55

Try this...

DECLARE	@CustIDs TABLE (CustomerID INT)

INSERT	@CustIDs (CustomerID)
SELECT	CustomerID
FROM	Customer
WHERE	[Status] = 0

DECLARE	@CurrID INT,
	@MaxID INT

SELECT	@CurrID = MIN(CustomerID),
	@MaxID = MAX(CustomerID)
FROM	@CustIDs

WHILE @CurrID <= @MaxID
	BEGIN
		EXEC spsend @CurrID

		SELECT	@CurrID = MIN(CustomerID)
		FROM	@CustIDs
		WHERE	CustomerID > @CurrID
	END

Peter Larsson
Helsingborg, Sweden

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-03-08 : 04:10:19

quote:
Originally posted by khtan

Don't LOOP ! Change your stored procedure !

Why?
The spsend stored procedure may be mission critical and is called from several other places with one single CustomerID only.

And this new SP is designed to work with all Customers having Status = 0.

Peter Larsson
Helsingborg, Sweden

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-03-08 : 05:00:20

quote:
Originally posted by Peso

quote:
Originally posted by khtan

Don't LOOP ! Change your stored procedure !
Why?
The spsend stored procedure may be mission critical and is called from several other places with one single CustomerID only.

And this new SP is designed to work with all Customers having Status = 0.

Peter Larsson
Helsingborg, Sweden

in that case, create a new SP out of the old one that handle the status for all customers

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources