By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
457,954 Members | 1,152 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 457,954 IT Pros & Developers. It's quick & easy.

"for" loop

P: n/a
hello

Does exist in SQL language "for" loop ? If yes, what syntax does it has ?

best wishes
Adam
Aug 17 '05 #1
Share this Question
Share on Google+
9 Replies


P: n/a
No - see WHILE and "Cursors" in Books Online. But in SQL, a loop is
often not a good solution - people may use them when they should use a
single, set-based statement such as UPDATE or DELETE.

Of course, there are times when a while or cursor loop is the best
solution, so you might have a good reason to do this - if you can give
more details of what you need to achieve, someone may be able to
comment on whether a loop is appropriate for you or not.

Simon

Aug 17 '05 #2

P: n/a
Mostly it pays to find a set based solution rather than write loops. If
you need to loop then TSQL has WHILE.

--
David Portas
SQL Server MVP
--

Aug 17 '05 #3

P: n/a
I try to substitute "for" loop by this excample:

declare @i int
while (@i<10)
begin
insert into dbname.dbo.table_name values @i
@i=@i+1
end

but in line @i=@i+1 ocured an error. Why ?
I know - it is stupid reason to use "for" loop , but what shoul I changed to
work this example ?
What are better ways to do such task ?

Aug 17 '05 #4

P: n/a
A common solution is to keep a table of numbers in your database
(single column of numbers from 0 to some very large number). This can
help avoid loops in many places:

INSERT INTO table_name (...)
SELECT num, ...
FROM Numbers
WHERE num BETWEEN 1 AND 10

Of course you'll still probably use a loop to populate the Numbers
table, but that only has to be done once and at install time, not at
runtime.

--
David Portas
SQL Server MVP
--

Aug 17 '05 #5

P: n/a
Numbers table:

CREATE TABLE numbers (num INTEGER NOT NULL CONSTRAINT pk_numbers
PRIMARY KEY) ;

INSERT INTO numbers (num) VALUES (0) ;

WHILE (SELECT MAX(num) FROM numbers)<65535
INSERT INTO numbers
SELECT num+(SELECT MAX(num)+1 FROM numbers)
FROM numbers ;

--
David Portas
SQL Server MVP
--

Aug 17 '05 #6

P: n/a
As David suggested, a numbers table is definitely a useful tool:

http://www.aspfaq.com/show.asp?id=2516

A calendar table is another good one:

http://www.aspfaq.com/show.asp?id=2519

Simon

Aug 17 '05 #7

P: n/a
Hi David,
We can do this simple trick

Select identity(int,1,1) MyId into MyTable from
anySystemTable,anySystemTable,anySystemTable .......

With warm regards
Jatinder Singh

Aug 17 '05 #8

P: n/a
adam (er******@wp.pl) writes:
I try to substitute "for" loop by this excample:

declare @i int
while (@i<10)
begin
insert into dbname.dbo.table_name values @i
@i=@i+1
end

but in line @i=@i+1 ocured an error. Why ?


You need to say SET or SELECT in front. I prefer SELECT, as you can assign
more that one variable in one bang:

SELECT @a = 1, @b = @c + d, @t = NULL


--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp

Aug 17 '05 #9

P: n/a
Here is a way to gemerate the Sequence table without proprietary code
and create the rows in parallel instead of one at a time.

WITH
(SELECT 0
UNION ALL
SELECT 1
..
UNION ALL
SELECT 9) AS Digits(digit)
SELECT D1.digit + 10*D2.digit + 100*D3.digit + 1000*D4.digit + ..
FROM Digits AS D1, Digits AS D2, Digits AS D3, Digits AS D4, ..
WHERE (D1.digit + D2.digit + D3.digit + D4.digit + ..) > 0;

IDENTITY has be done one at a time, while this can be parallelized and
can generate numbers in sets. The CTE can be repalced with a TABLE or
VIEW.

Aug 17 '05 #10

This discussion thread is closed

Replies have been disabled for this discussion.