limitation to UDF parameters (returns table)?

Question

Post reply

limitation to UDF parameters (returns table)?

kevin mann

Hall of Fame

Points: 3894
More actions
March 31, 2006 at 10:28 am

#168335

howcome I can do something liek this...

SELECT dbo.ReturnVal((SELECT MAX(val) FROM Table))

but I cannot do this...

SELECT * FROM dbo.ReturnTable((SELECT MAX(val) FROM Table))

I get this error:

Server: Msg 170, Level 15, State 1, Line 1

Line 5: Incorrect syntax near '('.

Viewing 9 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply

Jeff Moden SSC Guru Points: 1000539 More actions · Answer 1

If the UDF returns a scalar value instead of a table datatype, it would be like saying...

SELECT * FROM ((1))

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
"Change is inevitable... change for the better is not".

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)
Intro to Tally Tables and Functions

Sergiy SSC Guru Points: 110192 More actions · Answer 2

Sergiy

SSC Guru

Points: 110192

April 2, 2006 at 4:26 pm

#630304

You are trying to supply a table as a parameter.

Does not matter that this table has only 1 row, it's a table anyway.

And it's not allowed because SQL Server does not support 3D-tables.

DECLARE @p

SELECT @p = MAX(val) FROM Table

SELECT * FROM dbo.ReturnTable(@p)

_____________
Code for TallyGenerator

kevin mann Hall of Fame Points: 3894 More actions · Answer 3

Thank you for your responses gentlemen. I was hoping to use the table function in a view, so using variable(s) won't work for me. I am unsure about the 3D explanation, because that would apply to using a SELECT in a value-returned function as well.

If I try to plug in a multi-row returning SELECT to a value returned function I get this error:

Server: Msg 512, Level 16, State 1, Line 1

Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, , >= or when the subquery is used as an expression.

So obviously there's a trap programmed into SQL.

Kevin

Vladan SSC-Insane Points: 22072 More actions · Answer 4

I'm a bit confused... there seem to be two different things discussed here:

- passing a table as parameter into function - that is where you have problems

- output table as a result of UDF - that's something you can do. For example, you can pass one company ID into UDF and it returns a list of companies that have similar addresses.

To make it even more simple I've cut down the select to "select 4". Function that returns value can be called like

select dbo.returnvalue((select 4))

without any errors, while function returning table refuses the same syntax:

SELECT * FROM dbo.ReturnTable((select 4))

I think Sergiy was very close to the explanation with the 3-D tables, in fact it is very probable that he answered the question, but somehow I can't make it out. Could you elaborate a bit on that, Sergiy? I understand that we can only have one value in the parameter, but why table valued functions refuse select even if it returns one value?

BTW, you could find some info in BOL (topic "Inline User-Defined Functions"), maybe it helps you with the combination of views and functions...

HTH, Vladan