Loop on all rows in a view - 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

General SQL Server Forums

New to SQL Server Programming

Loop on all rows in a view

Author

Topic

1sabine8
Posting Yak Master

130 Posts

Posted - 2007-12-11 : 05:54:42

Hi, i have a Stored Procedure that is calling a view. I need to pass on all the records of this view so i made another view that returns the rows count and then a for loop from 0 to count-1. But then how can i access the rows and columns of the view one by one? Any idea?
And if someone has a better logic that gives the same result please let me know. Thanks...

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-12-11 : 06:01:14

Yes there are better ways.

First of all, why do you think you need to process all records one by one?

E 12°55'05.25"
N 56°04'39.16"

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2007-12-11 : 06:06:05

quote:
Originally posted by Peso

Yes there are better ways.

First of all, why do you think you need to process all records one by one?

E 12°55'05.25"
N 56°04'39.16"

OP might be front-end-developer-turned-backend-developer

Madhivanan

Failing to plan is Planning to fail

1sabine8
Posting Yak Master

130 Posts

Posted - 2007-12-11 : 06:09:35

I need to process them one by one because i need to take the fullName and pass it as a parameter to another procedure i'm calling in the same procedure. Any idea about the loop?

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-12-11 : 06:11:13

Without having the faintest idea of how your view looks like nor the two stored procedures, I would guess something lile this.

DECLARE @FullName VARCHAR(200)

SELECT @FullName = MIN(FullName)
FROM Table1

WHILE @FullName IS NOT NULL
BEGIN
EXEC sp2 @FullName

SELECT @FullName = MIN(FullName)
FROM Table1
WHERE FullName > @FullName
END

E 12°55'05.25"
N 56°04'39.16"

jezemine
Master Smack Fu Yak Hacker

2886 Posts

Posted - 2007-12-11 : 08:25:20

perhaps the proc could be re-written so it just does its thing on the table, rather than each row separately?

this sort of processing you are doing is discouraged. to help you though, we need to know what this proc is doing.

elsasoft.org

1sabine8
Posting Yak Master

130 Posts

Posted - 2007-12-11 : 09:09:23

i think i will need to use cursor.
Anyway thanks guys for trying to help me...

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-12-11 : 09:10:44

What?

Did you even look at the suggestion made to you posted 12/11/2007 : 06:11:13 ?

E 12°55'05.25"
N 56°04'39.16"

1sabine8
Posting Yak Master

130 Posts

Posted - 2007-12-12 : 09:01:13

yes thanks but i'm not dealing with min and max.

DonAtWork
Master Smack Fu Yak Hacker

2167 Posts

Posted - 2007-12-12 : 09:02:14

did you know that "dealing" with min and max is MUCH easier than "dealing" with a cursor?

[Signature]For fast help, follow this link:
http://weblogs.sqlteam.com/brettk/archive/2005/05/25.aspx
Learn SQL or How to sell Used Cars
For ultra basic questions, follow these links.
http://www.sql-tutorial.net/
http://www.firstsql.com/tutor.htm
http://www.w3schools.com/sql/default.asp

jezemine
Master Smack Fu Yak Hacker

2886 Posts

Posted - 2007-12-12 : 09:25:52

Peso, why is your loop any better than a cursor?

elsasoft.org

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources