I have a table-valued function and it returns a set of id data. I want to put the function in my query;
SELECT *
FROM caritbl
WHERE cari_id IN (dbo.fn_AtamaliCariListe(37))
Any suggestions?
Edit: I am using SQL Server
-
Im sorry I forgat to mention. I revised my question you can see it now.aykut aydoğan– aykut aydoğan2015-08-12 10:58:34 +00:00Commented Aug 12, 2015 at 10:58
Add a comment
|
4 Answers
In this case you can use this function like a table. Normally you perform your query writing like;
select * from XTable x where x.col in (select y.col from YTable y)
when you use Table-valued functions also you can do it in the same way;
select * from XTable x where x.col in (select y.col from dbo.fn_YourFunction(idparameter) y)
Not that your table-valued function ought to return a result with a column named "col"
Comments
Just do a SELECT with the function.
--Sample Table
SELECT 12 AS A
INTO #Temp
--Query here
SELECT * FROM #Temp WHERE A IN(
SELECT [transform].[Maximum] (10,12))
This should do the trick
Comments
Aykut's answer is the best answer under most circumstances. This is to note that the equivalent join is more complicated than the in. It would be:
SELECT DISTINCT c.*
FROM caritbl c JOIN
dbo.fn_AtamaliCariListe(37) as acl(id)
ON c.cari_id = acl.id;
Note the DISTINCT. Unless we know that the function returns unique values, we need it.
Now, there may be cases where the JOIN is actually better. If the function is non-deterministic, it is possible that SQL Server will re-evaluate it repeatedly for each row processed by the IN. I don't know if SQL Server would do this, but it is possible. A JOIN does guarantee that the function only gets evaluated once.
Comments
You could always join to it.
select yourfields
from yourtable join yourfunction on something
where whatever
This is logically equivalent to what you say you want to do.
Comments
