Skip to content
Advertisement

Loop through a table valued function

I have a table x which has columns y and z it has 10 rows of data.

I then have a table-valued function getDetails(y,z) which takes values stored in table x in columns y and z to return a table tb with three columns.

I need to write a tsql query that loops values from table x through function getDetails(y,z)to return one row per set of values y and zfrom table x in this case table tb should have 10 rows.

This is what I have so far which returns one row only each time I run it.

DECLARE @y NVARCHAR(50);
DECLARE @z NVARCHAR(2);

SELECT @y =y FROM x

SElECT @z =z FROM x


SELECT * from getDetails(@y,@z)

This returns

       col1      col2     col3
       ---------------------
row1   Lorem     Ipsum     Sit

//UPDATE If anyone has an idea how I can use temp tables to achieve this please let me know

Advertisement

Answer

You are trying to call user defined function for every row of table x. you dont need looping, you can go for set based operations itself.

Below I am creating table user defined function, which returns a table with 3 columns.

create FUNCTION dbo.testtableresult(@a int)
returns table as
return
select @a+1 as a1, @a+2 as a2, @a+3 as a3 

I am loading two rows into table variable and then calling the user defined function for each row.

DECLARE @table table(a int)

INSERT INTO @table values(1),(2)

SELECT t.a, t1.a1, t1.a2, t1.a3
FROM @table as t
CROSS APPLY dbo.testtableresult(t.a) as t1
+---+----+----+----+
| a | a1 | a2 | a3 |
+---+----+----+----+
| 1 |  2 |  3 |  4 |
| 2 |  3 |  4 |  5 |
+---+----+----+----+
7 People found this is helpful
Advertisement