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 z
from 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 |
+---+----+----+----+