On Thu, 28 Oct 2004 22:09:03 -0700, Saqib Saleem <Saqib
[quoted text, click to view] Saleem@discussions.microsoft.com> wrote:
>Hi,
>
> Can we use Loops in Stored Procedure of SQL Server 7.0.
Hi Saqib,
Yes, we can:
WHILE (some condition)
BEGIN
statements to be repeated
END
[quoted text, click to view] >How to Excess the results of a Query Which returns more than one record?
Typically, you don't. SQL is a set-based, declarative language, This means
that you describe WHAT the result should be, not HOW to get at the result.
How this is done will be figured out by the optimizer, which has some
nifty tricks up it's sleeve to enhance performance. It's safe to say that
in at least 99.99% of all cases, set-based SQL will outperform any
equivalent code that loops through result sets.
However, there are cases where set-based is not possible. Sending e-mail
from SQL Server is a good example - the system stored procedure that does
this takes parameters to send out one e-mail at a time, so you'll have to
call it repeatedly.
If you really must use a loop in your code, you have two options:
1. Put the select statement in a cursor definition, fetch the rows one by
one. This is probably the easiest to get done, but do take the time to
look into the various options on the cursor declaration. You can find much
information about cursors in Books Online.
2. Change the SELECT statement to return only one row at a time and
include a WHERE clause that ensures it's the "next" row. This is only
possible if the result set has at least one column that is unique within
the set; ordering on that column should not influence the effect of the
loop (in the e-mail example, it doesn't; there are examples where the rows
returned from a select statement have to be processed in a very specific
order). A rough sketch of how this would look:
SELECT @UniqueCol = UniqueCol, @OtherCol = OtherCol, ...
FROM MyTable
WHERE something = somethingelse
AND UniqueCol > @UniqueCol -- Must be beyond "previous" row
ORDER BY UniqueCol
And use @@ROWCOUNT > 0 as the WHILE ocndition to find out when this query
doesn't return any data anymore.
Note that this will hurt performance. In a worst case scenario, no index
can be used to carry out the order by: a sort is needed. If the query
returns 100 rows, you'll have to execute this query 101 times; the first
will need to sort 100 rows, the second 99, and so on. That will still be
fast. But what when the number of rows is 100,000? Sorting 100,000 rows
does take some time - and doing 100,000 sorts of (on average) 50,000 rows
will cause a very notable performance impact!!
There are ways to get around that - and the best way is to not use looping
at all, but use set-based declarative SQL instead.
Best, Hugo
--
