Using a select query as parameter in a table-valued function

Question

I have a function that takes an integer input and returns a table with the related ids from the below table.

ID  NAME    RELATED_ID
1   a       null
2   b       null
3   c       1
4   d       1

So, select * from fn_getrelatedids(1) returns

ID
1
3
4

I want to use a dynamic parameter as a parameter to the function, i.e.,

select * from fn_getrelatedids (select e.id from entity e where e.name='a')

Obviously this doesn't work.

I tried cross apply:

select e.id from entity e 
cross apply 
(select f.id from fn_getrelatedids (e.id) AS fg
WHERE fg.id = e.id) AS R
WHERE e.name = 'a'

That doesn't return the correct value. The function returns only

ID
1

Am I missing something obvious here?

score 2 · Accepted Answer · edited Feb 19 '16 at 15:58

2

See if this isn't what you are trying to do.

select fg.id 
from entity e 
cross apply dbo.fn_getrelatedids(e.id) AS fg
WHERE e.name = 'a'

Edit:to add the right select

edited Feb 19 '16 at 15:58

Pindub_Amateur

328
1
6
19

answered Feb 19 '16 at 15:17

Sean Lange

33,028
3
25
40

I'm not sure why, but the result had the correct number of rows, but repeated values, ID 1 1 1 – Pindub_Amateur Feb 19 '16 at 15:23
You might want to read up on APPLY and how it works. Paul White has a fantastic 2 article series on this. http://www.sqlservercentral.com/articles/APPLY/69953/ and http://www.sqlservercentral.com/articles/APPLY/69954/ – Sean Lange Feb 19 '16 at 15:25
The "repeated" values would be because there were apparently 3 rows returned from your function and the ONLY value in your select statement is e.id. Since you didn't provide and ddl for your tables or the function and no sample data I am guessing. If you can post these details we can figure out what you really want. – Sean Lange Feb 19 '16 at 15:27
So change the select statement to select the value you want. I am guessing in this case it would fg.id? You have to remember I can't see your screen and I have no idea what that function does. I am guessing based on a vague description of what you want. – Sean Lange Feb 19 '16 at 15:34
` SELECT egt.group_id, fg.* FROM vsabuser.entity_group_translation egt CROSS APPLY vsabuser.fn_GetRelatedGroupsAndSelf(egt.group_id) AS fg WHERE egt.name = 'Insurance' ` – JamieD77 Feb 19 '16 at 15:36
@JamieD77 what is that? None of those objects are in this question anywhere?? – Sean Lange Feb 19 '16 at 15:38
@SeanLange yeah it's in his last comment. after "something like this:" – JamieD77 Feb 19 '16 at 15:39
I give up. If you won't post the function this is hopeless. I showed you how to use a table valued function. – Sean Lange Feb 19 '16 at 15:41
The sample data is present in the question with what I'm expecting in the result. I'm expecting the function to return the values 1, 3, 4. Basically I want just a single value passed into my function. So I need the first sql query to be executed before it's passed into the function. Something like this: select e.id from entity e where e.name = 'a' cross apply (select fg.* from fn_getrelatedids(e.id) AS fg). Obviously, this doesn't work. But just to give an idea. – Pindub_Amateur Feb 19 '16 at 15:42
But you STILL have not shared your function. http://weblogs.sqlteam.com/jeffs/archive/2008/05/13/question-needed-not-answer.aspx – Sean Lange Feb 19 '16 at 15:46
I can't actually post the function because it's too long. But it basically takes an ID to find all the IDs it's related to. Thanks for the link. I'm reading through to get a better understanding of APPLY. But your last comment got me the solution. I just needed to change my select query. Stupid mistake on my part. – Pindub_Amateur Feb 19 '16 at 15:48

score 0 · Answer 2 · edited Aug 23 '19 at 09:26

Example:

SELECT      table1.f_code, 
            table1.subsidiaryledger AS ledgerid, 
            table1.subgroupid , 
            table1.openingamt, 
            table1.ac_type, 
            0 AS debitamount, 
            0 AS creditamount, 
            closing , 
            closing_type 
FROM        ledgersubsidiaryopeningbalanace   AS table1 
** cross apply 
dbo.getsubsidaryclosingnew (table1.subsidiaryledger,table1.subgroupid,table1.openingamt,table1.ac_type,0,0) AS tv
WHERE       table1.subsidiaryledger = tv.ledgerid 
AND         table1.subgroupid = tv.subgroupid** 
AND         subsidiaryledger = 15105 
AND         f_code = 1

Please add explanations to your answer. See https://stackoverflow.com/help/how-to-answer — jasie, Aug 23 '19 at 09:39

Using a select query as parameter in a table-valued function

2 Answers2