hello
Does exist in SQL language "for" loop ? If yes, what syntax does it has ?
best wishes
AdamNo - 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|||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
--|||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 ?|||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
--|||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
--|||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|||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|||adam (ereuseen@.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, esquel@.sommarskog.se
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||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.
