Stored Procedures - How do I Loop through results

[Ashquatok]

I have several tables that are linked together by a common value and want to create a stored procedure that will perform a query and then go through each of the returned results one at a time and perform some functions.

My problem is that I do not know how to set up a stored procedure so that it will loop through the results one at a time. Usually SQL statements in stored procedures only return the last result of the query.

How do I loop through them all? I assume there is a loop function that has been evading my eyes, dammit it must be found. [sig][/sig]

 

[chlorineKid]

You probably need to use a cursor for what you are trying to do: Look up cursor in BOL and it will explain the syntax, etc.

CK [sig][/sig]

 

[jnicho02]

chlorineKid is correct....but, before you use a cursor always check that you really can't do it with standard SQL. It is important to think differently when programming in SQL as it is set-based. Stepping through records one by one and then doing something can be quite slow. [sig]<p> <br><a href=mailto: > </a><br><a href=

http://www.geocities.com/jnicho02/>my

home</a><br> [/sig]

 

[chlorineKid]

Yes, I'd agree with that. I always avoid cursors unless there is NO other way to solve the problem. They are extremely slow so they are best avoided.

CK [sig][/sig]

 

[jcrivera]

Moreover I've had to use cursors as the last bullet to kill the mockingbird because sometimes I find a weird situation in which, for example, I try to make an update that I know it will modify more than one row, but get as an error that a supposed subquery is returning more than one value. This makes me sick, because in standard SQL this could be performed, but dont know why sometimes a sp done in SQL Server bombs like a dynamite when trying to do the same thing.

When I use cursors, although slower, at least it works.

J.C. [sig][/sig]

 

[shabnam]

I have found that you can generally do the same using a temp. table. Populate the table with your select query adding a sort field which can populate with unique values(something like an identity col or a join of a unique id and date). The you can loop thorough the rows of the table by selecting on the basis of the sort field. let's say your sort field is an integer starting from 1, you could say -
declare @sort int
select @sort = 0
while 1 = 1 (endless loop so you need to break out)
begin
select min(sort)
from #temp1
where sort > @sort

if @sort is null or @@rowcount = 0
break (comes out when all the rows have been processed)

select * (select all the columns into variables)
from #temp1
where sort = @sort

<do your processing with the data for this row
end /** end of your while 1 = 1 loop **/


[sig][/sig]

 

[simma]

Thanks shabnam!!I have same kind of problem and got some idea.I have to select somerecords from table1 and loop through each record and find matching records in table2 and finall inserting matched records into temptable.Can u give some idea as to how to proceed?
Thanx

 

[tlbroadbent]

simma,

As jnicho02 and ChlorineKid said, there is often no need to loop through records because the same function can be done with SQL. Usually, selecting records from table1 and finding matching records in table2 and then inserting into a temp table can be handled in one SQL statement. table1 and table2 must have a relationship based on a common column or columns in order for this to work.

Example:
Insert Into #temptable (col1, col2, col3, col4)
Select t1.col1, t1.col3, t2.colA, t2.colX
From table1 As t1 Inner Join table2 As t2
On t1.col1=t2.colA
And t1.col3=t2.colB Terry L. Broadbent
FAQ183-874 contains tips for posting questions in these forums.
NOTE: Reference to the FAQ is not directed at any individual.

 

[simma]

Thanks Terry
But does this loop through evryrecord in table A subsequently finding matching records in Table B? I'm Trying your query anyways.
Thanks again

 

[tlbroadbent]

No, it doesn't LOOP through a table. It does find a SET of records that have a common attribute. The example I gave used an INNER JOIN. If you want to select data from every record on TableA and also get data from matching records on TableB, you'll need to perform an OUTER JOIN or in this example, a LEFT JOIN.

Example:
Insert Into #temptable (col1, col2, col3, col4)
Select t1.col1, t1.col3, t2.colA, t2.colX
From tableA As t1 Left Join tableB As t2
On t1.col1=t2.colA
And t1.col3=t2.colB

In this case col3 and col4 of #temptable could be NULL if no matching record exists in TableB. The type of JOIN you use depends on the result you desire. I suggest reading about table relationships and JOINS in SQL BOL or other sources. Terry L. Broadbent
FAQ183-874 contains tips for posting questions in these forums.
NOTE: Reference to the FAQ is not directed at any individual.

 

[simma]

Thanks Terry
But I have to loop through each record form table A to search as I have lot of other stuff to do during each search.
Thanx

 

[tlbroadbent]

Depending on the nature of the &quot;lot of other stuff&quot; you may still be able to do what you need without looping. SQL can do &quot;a lot of other stuff.&quot; It is very good at manipulating data.

As has been mentioned, there are times when a cursor and procedural looping is required. But it should be avoided where possible. Without knowing the nature of your application, I merely want to bring to your attention the fact that SQL can do much more than most people realize. We simply have to learn a different approach to programming. Terry L. Broadbent
FAQ183-874 contains tips for posting questions in these forums.
NOTE: Reference to the FAQ is not directed at any individual.

 

[simma]

Thanks!!