How to call Inline Table Function with output parameter from scalar function?

Question

Hi guys,

I would like to know if it's possible to call a inline function with an output parameter from a scalar function?

For example: My inline function is called test_1(input parameter format bigint), the following example works great:

SELECT * FROM MM.test_1(4679)

But now I would like to use it with an output parameter from a scalar function called dbo.test_2(output parameter format bigint) to make it more dynamically, but it don't works?

SELECT (SELECT * FROM MM.test_1(dbo.test_2(kp.id))) FROM kp kp WHERE id = 4679

I receive the following error message:
"Error in list of function arguments: '.' not recognized. Incomplete parameters list. Missing FROM clause. Unable to parse query text."

Any suggestions?

Any help will be appreciated!

Thx forward, Best Regards Andreas

Answer 1

SELECT * FROM MM.test_1(dbo.test_2(4679))

This part should work fine if you have a table-valued function test_1 and a scalar function test_2. I can only test in MSSQL 2008 now, but as far as I know MSSQL 2000 should like it too.

How-eeever.. what do you expect your sample query to do? As far as I can tell you're selecting the output of MM.test_1 from kp... which doesn't make sense.

Answer 2

Hi,

The following statement is static because of the value 4679:
SELECT * FROM MM.test_1(dbo.test_2(4679))

Now I want to make it dynamically, so I tried to use the required value from column id of the table KP
SELECT (SELECT * FROM MM.test_1(dbo.test_2(kp.id))) FROM kp kp

I hope you understand what I mean?!?

Thank you for your effort, BR Andreas

Answer 3

I can interpret what you're trying to do in two different ways.

Scenario 1:

You want to get a single value out of your kp table first, then run it through the static function, then feed the result to the inline function and get a table.

In this case, you can do something like:

declare @foo int;
set @foo = (select kp.id from kp where blahblahblah);
select * from MM.test_1(dbo.test_2(@foo))

Scenario 2:

More likely, for each record in your kp table, you want to do all those nasty things to its id and union the resulting tables.

In this case, the best I can suggest would be some dynamic SQL that goes through your id values, using a cursor, computes the partial results, then UNIONs it all together. Google for using cursors to loop through tables and you'll find enough to get you going (like this post). You must think of your table-valued function like a (surprise...) table. You cannot use it as a column in a SELECT statement in the same way you could use a static function and also you cannot feed the fields of a query back to themselves.

If you are calling this SQL from an application you should seriously think about doing this in code instead.

Good luck!