sql union problem

Question

Dear All in this SQL code

DECLARE @n tinyint
   WHILE (@n > 0)
   BEGIN
    SELECT  @n AS 'Number'
      ,CASE
        WHEN (@n % 2) = 1
          THEN 'EVEN'
        ELSE 'ODD'
       END AS 'Type'
    SET @n = @n - 1
   END

How could I put union clause in this could to have the result shown in one result set?

Answer 1

you could just do this:

DECLARE @n TINYINT
SET @n = 100
SELECT  number,
     CASE WHEN (number % 2) = 1 THEN 'EVEN' ELSE 'ODD' END AS 'Type'
FROM    (
      SELECT ROW_NUMBER() OVER(ORDER BY number) AS number
      FROM master..spt_values
     ) t
WHERE   number < @n

Answer 2

Try the following:

DECLARE @n TINYINT
DECLARE @sql VARCHAR(max)
SET @sql=''
SET @n=10
   WHILE (@n > 0)
   BEGIN
  SET @sql=@sql+' SELECT  '+CONVERT(VARCHAR(50) ,@n)+' AS ''Number''
      ,CASE
        WHEN ('+CONVERT(VARCHAR(50) ,@n)+' % 2) = 1
          THEN ''ODD''
        ELSE ''EVEN''
       END AS TYPE '
       IF @n>1 SET @sql=@sql+' Union '
    SET @n = @n - 1
   END

   EXEC( @sql)

Answer 3

You can even try this

DECLARE @n tinyint
declare @tbl table(number tinyint,NumberType varchar(10))

set @n  = 10
   WHILE (@n > 0)
   BEGIN
   insert into @tbl

    SELECT  @n AS 'Number'
      ,CASE
        WHEN (@n % 2) = 0
          THEN 'EVEN'      
       END AS 'Type'

       UNION

       SELECT  @n AS 'Number'
      ,CASE
        WHEN (@n % 2) <> 0
          THEN 'ODD'
       END AS 'Type' 

    SET @n = @n - 1

   END
   select * from @tbl where NumberType is not null

The output is

**number    NumberType**
10  EVEN
9   ODD
8   EVEN
7   ODD
6   EVEN
5   ODD
4   EVEN
3   ODD
2   EVEN
1   ODD

But what you actually want to achieve?

Answer 4

It is easier to rethink your problem. Think in sets!

First generate the set of numbers. Then clasify the set of numbers as Even or Odd.

declare @numberCount int 
set @numberCount = 10;

with numbers as
(
  select 1 as number
  union all
  select number + 1 from numbers where number < @numberCount
)

select number,
       case        
          when (number % 2) = 1     
             then 'EVEN'      
             else 'ODD'      
       end AS 'Type' 
 from numbers option(maxrecursion 10000)