Union Select in a loop - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Union Select in a loop

Author

Topic

ten2the6
Starting Member

7 Posts

Posted - 2004-10-07 : 11:13:34

Hey everyone ...

Here is what I have today ...

declare @recCount int
set @recCount = 1
while(@a<@b)
begin
if( @recCount = 1 )
select @recCount
else
union select @recCount
--correction
set @recCount = @recCount + 1
end

RESULTS in
Incorrect syntax near the keyword 'union'.

What I expected was to see

ids
------
1
------
2
------

I do not want to use temp tables.

Please help!

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2004-10-07 : 11:30:18

quote:

I do not want to use temp tables.

Why not? You could use a table variable. Or store a permanent table of Numbers in your database and just access it when you need to (that will be the most efficient way).

- Jeff

surefooted
Posting Yak Master

188 Posts

Posted - 2004-10-07 : 11:35:12

From what you have posted, you set @recCount =1, but never change it during your loop so your if statement will always be true. Also, that is an incorrect use of 'union.' Can you post more of your code, or more of what your trying to do?

-Jon
Now a "Yak Posting Veteran".

VIG
Yak Posting Veteran

86 Posts

Posted - 2004-10-07 : 12:05:46

select 10*a+b+1 nums
from
(select 0 a union all select 1 union all select 2 union all select 3 union all select 4 union all
select 5 union all select 6 union all select 7 union all select 8 union all select 9) as a,
(select 0 b union all select 1 union all select 2 union all select 3 union all select 4 union all
select 5 union all select 6 union all select 7 union all select 8 union all select 9) as b
where (10*a+b)<= @cnt
order by 1

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources