0

I'm getting syntax error when trying to execute table valued function.

select * from fn_security(select R.rk from dbo.LINK R)

Error:

Msg 156, Level 15, State 1, Line 1
Incorrect syntax near the keyword 'select'.
Msg 102, Level 15, State 1, Line 1
Incorrect syntax near ')'.

What am I doing wrong? how to execute this?

neer
  • 4,031
  • 6
  • 20
  • 34
AskMe
  • 2,495
  • 8
  • 49
  • 102

1 Answers1

2

You can't pass whole table,like the way,you are trying now..you can use cross apply to get all

you can try below 

select * from dbo.LINK  r
cross apply
dbo. fn_security(r.rk)
TheGameiswar
  • 27,855
  • 8
  • 56
  • 94
  • So, that means, 'cross apply' will give the same result if "fn_security" would have a table instead of function? That is , for example, select * from table_security(select R.rk from dbo.LINK R) is same as [in terms of getting results] select * from dbo.LINK r cross apply dbo. fn_security(r.rk) – AskMe Nov 01 '16 at 08:46
  • `You can't pass whole table` - what about user-defined table types? `You can use cross apply` - what about outer apply? – gotqn Nov 01 '16 at 08:48
  • Think of cross apply as join,but you can reference the main table columns ,so if your join yields multiple results,then multiple values – TheGameiswar Nov 01 '16 at 08:48
  • 1
    @TryingBest - you're the person who has both the table `LINK` and the function `table_security`. We don't. You at least can *try* the query and see what it produces. If it doesn't produce *what you expect*, then I'd suggest editing your question and including sample data and expected results – Damien_The_Unbeliever Nov 01 '16 at 08:48
  • @gotqn: i am not sure ,i understand totally `what about outer apply? ` ,user defined table types can be used for procedues only – TheGameiswar Nov 01 '16 at 08:49
  • if you use `cross apply` and the function does not return a value, the corresponding row will not be returned either (it's like inner join), if you use `outer apply` all rows will be returned (even thought the function does not return value(s) when being executed with the corresponding row column value. Also, check - http://stackoverflow.com/a/1609200/1080354 – gotqn Nov 01 '16 at 08:54