limitation to UDF parameters (returns table)?
-
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 '('.
-
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) -
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 -
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
-
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
-
When optimizer parses query it cannot tell how many records will be returned.
So it treats SELECT as an array, no matter what you have in mind.
Table function desined to return a table from a value.
So it suppose to return table for each value in array supplied as a parameter.
It gives you 3D-array.
And 3D array stays 3D array even if one or two of its dimesions limited by 1 unit.
SQL Server does not support 3D arrays, that's why it does not allow to use any kind of SELECT as a parameter for table or inline function.
_____________
Code for TallyGenerator -
BTW, table UDF is a View with parameter.
If you want to have a VIEW dependable on any parameter you don't need to inbiuld table UDF in this VIEW, you need just transform VIEW to table UDF.
_____________
Code for TallyGenerator -
huh?
-
so I am just taking it as a bug in SQL Server and moving on... I love finding these things, even though I can't do what I want.
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply