user defined function in a select

Hello,

i would like to get the result of a function in a select-statement depending on the result of an other column. I attached a little sample that works on the table syscolumns, so everybody should be able to check by now.
The only thing I get is an error function name not recognized

create Function fn_test( @N bigint)
RETURNS Table
AS
RETURN (
SELECT
@N + id as Result
FROM syscolumns
)

Sample SELECT:
Select typestat, fn_Test( typestat ) FROM syscolumns

Any hint would be very kind
Thanks in advance
Matthias

Answer this question

user defined function in a select

Conversion
Thank you, I see the mistake.
I need something like the OUTPUT-parameter in a PROCEDURE, but thats' not possible - or do you know a way
In the original function I expect 6 Values, in a single row therefore I choosed a table as the return value. As I read your answer everything with the error ist clear, but how to get the values from the function - I don't want to use a single Selects for all values because I use them in a report for each Row four times - so I would become 24 new selects and a very long statement with the opportunity for different mistakes.
swtpotato183
Hello,

thank you for your reply.
I've tested this already, if I do so I get an error (translated from german)
Message no 208
unknown objectname 'dbo.fn_Test'

The functions is shown in the function-section of the Query Analyzer as dbo.fn_Test
SQLServer Developer Edition Version is 8.00.760 (SP3)
on WIN XP

Any other idea

Regards
Matthias
sasa_msh
you need to prefix the function call with the owner name. . something like:

Select typestat, dbo.fn_Test( typestat ) FROM syscolumns
cpede
It must work with SqlServer 2000, therefore I've splitted the function and use now a single subselect in the main select for every value.

Thank you Steve and Blair Allen for your help

Matthias
LTaylor
ahh . . . good catch. I didn't look closely at the return value!
Prajakta
ummm. . . sanity check

confirm the function is owned by dbo. . .

run this query:

select u.name+'.'+ f.name
   from sysobjects f inner join sysusers u on
      f.uid = x.uid
   where f.xtype = 'fn'
BarKey
i've changed your select to
select u.name+'.'+ f.name from sysobjects f inner join sysusers u on f.uid = u.uid where f.xtype = 'if'
and i see the function listed
B Sutton
You could use SQL Server 2005's APPLY feature. While it would help to see your
CREATE TABLE statements, sample data and desired output, my guess is that you could do something like this:

select
T.typestat, F.colA, F.colB, F.colC
from myTable as T
cross apply fn_test(T.typestat)

SK
Nada Ali
The items in the SELECT list must be single-valued. In your example, you are using a table-valued function in the SELECT list. What result do you expect by specifying a table as one of your result columns The reason this doesn't work is because the function is table-valued.

Steve Kass
Drew University
user defined function in a select

Answer this question