FOR/NEXT/LOOP structure?

I'm just getting up on SQL and I have a very basic issue. How would you
implement a "loop" or FOR/NEXT directive in SQL. I have looked around and do
not see anything that seems like a likely command. Thanks in advance for you
r
input.> How would you implement a "loop" or FOR/NEXT directive in SQL.
There is WHILE/BEGIN/END. However, usually, you would do what you need to
do WITHOUT resorting to loops. SQL is based fundamentally on treating data
as sets rather than iterations. If you give us more details about what you
are trying to accomplish, instead of deciding that you need to use loops,
you will probably get better help and will be on your way to thinking in
sets instead of singletons.
Please post DDL, sample data and desired results.
See http://www.aspfaq.com/5006 for info.|||TSQL has WHILE loops.
Hopefully I don't need to tell you that Iterating through a table or
result set in SQL isn't a good idea. Best practice is to write your
data manipulation as set-based code without loops. If you need help to
do that, then please come back with some more information of what you
are trying to do.
David Portas
SQL Server MVP
--|||We are making a big jump fromthe xBase world... my brain is still adjusting.
In my database I have tmpTable1...tmpTable99 and mastertable
I want to do an INSERT INTO mastertable for each tmpTable
Although syntactically incorrect, Something like...
FOR tblCount = 1 TO 99
INSERT INTO mastertable SELECT (fields) FROM tmpTable##
DROP table##
NEXT tblCount
"Aaron [SQL Server MVP]" wrote:

> There is WHILE/BEGIN/END. However, usually, you would do what you need to
> do WITHOUT resorting to loops. SQL is based fundamentally on treating dat
a
> as sets rather than iterations. If you give us more details about what yo
u
> are trying to accomplish, instead of deciding that you need to use loops,
> you will probably get better help and will be on your way to thinking in
> sets instead of singletons.
> --
> Please post DDL, sample data and desired results.
> See http://www.aspfaq.com/5006 for info.
>
>|||In that case the real question is why do you have 99 separate tables to
start with? Apparently they are all the same structure so in the SQL
world it makes much more sense to have ONE table, if necessary with an
extra column for whatever attribute is represented by the number 1 -
99.
If this is just a one-off exercise then you can just cut and paste a
script of 99 INSERT statements. If this is something you feel the need
to do regularly then you should definitely reconsider your design - fix
whatever process creates these tables so that it just creates one table
instead.
If you are stuck with 99 separate tables and don't want to hard-code
the names then you will be forced to use a messy kludge with dynamic
SQL. Unlike xBase you can't easily parameterize table names in SQL
Server. Instead you have to build a command string and execute it like
a macro using the EXEC statement. That's one reason why good logical
database design is so important.
Dynamic SQL introduces a number of problems to do with performance,
scalability, maintenance and security. Don't go there if you can
possibly avoid it, and read the following article first:
http://www.sommarskog.se/dynamic_sql.html
David Portas
SQL Server MVP
--|||> In that case the real question is why do you have 99 separate tables to
> start with?
Hey, at least it seems he's trying to fix it. :-) And he can generate his
list of INSERT statements pretty easily, e.g. using Query Analyzer:
DECLARE @.i TINYINT
SET @.i = 1
WHILE @.i <= 99
BEGIN
PRINT 'INSERT mastertable(column_list) SELECT column_list FROM
tmpTable'+RTRIM(@.i)
SET @.i = @.i + 1
END
But I agree with everything else you said... hopefully this is a one-time
thing and not something that is going to become a part of the app.
Please post DDL, sample data and desired results.
See http://www.aspfaq.com/5006 for info.|||>> We are making a big jump fromthe xBase world... my brain is still
adjusting. <<
It will take at least a year to un-learn the old mental models.
That would be a UNION of all the tables. But this sounds like your
schema and appraoch are wrong. Instead of 99 identical tables, why not
one table with an integer column that holds whatever information is
need to show the source of the data?|||The WHILE loop worked nicely. Thanks for the input!
"The VanDerbeck Group" wrote:

> I'm just getting up on SQL and I have a very basic issue. How would you
> implement a "loop" or FOR/NEXT directive in SQL. I have looked around and
do
> not see anything that seems like a likely command. Thanks in advance for y
our
> input.

For XML Path

im using the ROOT directive in association with FOR XML PATH to return results. When the query returns no records then I get no root node either (which makes the XML invalid). Is there an attribute that specifies that the root node should always be returned (even when empty)?

i.e. <ROOT />

thx

Not that I know of. Since no rows were returned, not data would be returned at all. You could do something like this:

select cast(
'<root>' +

coalesce((select *
from sys.objects
where 1=2 --change to 1=1 to get rows
for xml path),'')

+ '</root>' as xml)

This does seem to work, though not 100% sure if there will be much of a performance hit.

|||

thanks for the tip :)

im am confused becuase if i say that i want a resultset typed as xml, then i would expect (for the xml to be valid) that it has a root node regardless..... what concept am i missing if this is not the case?

|||I think the fact is, it isn't invalid XML, it is nothing. So if you return no data, then no XML is created.|||

i agree

BUT :)

that does mean that anything that uses the resultset requires a condition to check if it is Null and either 1)do nothing, or 2) subsitute it for what would be valid xml i.e. an empty parent node eg <Root />

i think a lot of applications would require number 2, and therefore think the XML functionality of sql2005 should have this built in.

|||

And I don't disagree with you, though you can use the cast and concatenation thing I posted earlier as a workaround.

If noone posts that I was wrong, consider posting your suggestion here: https://connect.microsoft.com/SQLServer/Feedback and then post in this thread that you have, and I will vote for it.

|||

how about this:-

CREATE PROCEDURE [dbo].[up_DoStuff]
(
-- params
)
AS
SET NOCOUNT ON;

DECLARE @.pXML XML
SET @.pXML = (
SELECT
...
FROM
...
WHERE
...
FOR
XML Path('Test'),
ELEMENTS,
ROOT('Tests'),
TYPE
)

SELECT ISNULL(@.pXML, '<Tests/>')

For XML Path

im using the ROOT directive in association with FOR XML PATH to return results. When the query returns no records then I get no root node either (which makes the XML invalid). Is there an attribute that specifies that the root node should always be returned (even when empty)?

i.e. <ROOT />

thx

Not that I know of. Since no rows were returned, not data would be returned at all. You could do something like this:

select cast(
'<root>' +

coalesce((select *
from sys.objects
where 1=2 --change to 1=1 to get rows
for xml path),'')

+ '</root>' as xml)

This does seem to work, though not 100% sure if there will be much of a performance hit.

|||

thanks for the tip :)

im am confused becuase if i say that i want a resultset typed as xml, then i would expect (for the xml to be valid) that it has a root node regardless..... what concept am i missing if this is not the case?

|||I think the fact is, it isn't invalid XML, it is nothing. So if you return no data, then no XML is created.|||

i agree

BUT :)

that does mean that anything that uses the resultset requires a condition to check if it is Null and either 1)do nothing, or 2) subsitute it for what would be valid xml i.e. an empty parent node eg <Root />

i think a lot of applications would require number 2, and therefore think the XML functionality of sql2005 should have this built in.

|||

And I don't disagree with you, though you can use the cast and concatenation thing I posted earlier as a workaround.

If noone posts that I was wrong, consider posting your suggestion here: https://connect.microsoft.com/SQLServer/Feedback and then post in this thread that you have, and I will vote for it.

|||

how about this:-

CREATE PROCEDURE [dbo].[up_DoStuff]
(
-- params
)
AS
SET NOCOUNT ON;

DECLARE @.pXML XML
SET @.pXML = (
SELECT
...
FROM
...
WHERE
...
FOR
XML Path('Test'),
ELEMENTS,
ROOT('Tests'),
TYPE
)

SELECT ISNULL(@.pXML, '<Tests/>')