Hi!
I have the following build problem with UDF creating the following.
The simplified text does the following:
1> MSSQL2005 UDF

CREATE FUNCTION dbo.FUNC1(
@BitCnt INT,
@Mask INT
)
RETURNS @retVal TABLE (
Val INT
)
AS
BEGIN
DECLARE @i INT
DECLARE @MaxBit INT

SET @i = 0
SET @MaxBit = power(2,@BitCnt)

WHILE @i < @MaxBit
BEGIN
IF @i & @Mask = @Mask
INSERT @retVal SELECT @i
SET @i = @i + 1
END

RETURN
END

2> DB2V9.7(Fix1)
CREATE OR REPLACE FUNCTION dbo."_FCOMBitMask"
(
BitCnt INTEGER,
Mask INTEGER
)
RETURNS
TABLE
(
Val INTEGER
)
LANGUAGE SQL
MODIFIES SQL DATA
SPECIFIC dbo.FUNC1

BEGIN ATOMIC
DECLARE i INTEGER;
DECLARE MaxBit INTEGER;
SET i = 0;

DELETE FROM dbo.retVal;

SET MaxBit = POWER(2, BitCnt);
LOOP1: WHILE i < MAXBIT DO
IF bitand(i,Mask) = Mask THEN

INSERT INTO retVal ----> may be temporary table(Can't Work)
SELECT i
FROM SYSIBM.SYSDUMMY1 FETCH FIRST 1 ROW ONLY;

END IF;
SET i = i + 1;
END WHILE;
RETURN 0;
END@

Also I Know(UDF can't use global temp table)
1) Create Procedure
2) UDF Call Procedure
How Can I make the UDF the Another Method?

DB2 SQL Table Function have a restriction
that once a RETURN statement was executed, no control would be
returned to the function(as you know).

A way to avoid this restriction and to return multiple rows from a
table function
is to use a return statement with recursive common table expression.

Here is a sample...

------------------------- Commands Entered -------------------------
CREATE OR REPLACE FUNCTION dbo.FCOMBitMask
( BitCnt INTEGER
, Mask INTEGER
)
RETURNS TABLE
( Val INTEGER )
LANGUAGE SQL
READS SQL DATA
SPECIFIC dbo.FUNC1
RETURN
WITH while_loop( i , flag ) AS (
VALUES ( -1 , 'N' )
UNION ALL
SELECT i + 1
, CASE BITAND(i + 1 , Mask)
WHEN Mask THEN
'Y'
ELSE 'N'
END
FROM while_loop
WHERE i < 2147483647
AND i < POWER(2, BitCnt) - 1
)
SELECT i
FROM while_loop
WHERE flag = 'Y'
;
--------------------------------------------------------------------
DB20000I The SQL command completed successfully.

------------------------- Commands Entered -------------------------
SELECT *
FROM TABLE( dbo.FCOMBitMask(5 , 13) )
;
--------------------------------------------------------------------

VAL
-----------
13
15
29
31

4 record(s) selected.

Without considering conversion(and sequence of result values), the
function itself would be written with less number of iterations.

CREATE OR REPLACE FUNCTION dbo.FCOMBitMask
( BitCnt INTEGER
, Mask INTEGER
)
RETURNS TABLE
( Val INTEGER )
LANGUAGE SQL
READS SQL DATA
SPECIFIC dbo.FUNC1
RETURN
WITH loop1(k , ret) AS (
VALUES (0 , 0)
UNION ALL
SELECT k + 1
, ret + n
FROM loop1
, TABLE
(VALUES POWER(2 , k)
, CASE BITAND( POWER(2 , k) , Mask )
WHEN 0
THEN 0
ELSE NULL
END
) n(n)
WHERE k < 32
AND k < BitCnt
AND n IS NOT NULL
)
SELECT ret
FROM loop1
WHERE k = BitCnt
;