How to Call Function with pass select to param

Question

I have a function that has a parameter varchar.

I want to call the function and pass select result to function like

Select * from dbo.myfunc(select name from users)

But I get this ERROR:

Incorrect syntax near ')'

What can I do?

Your error is because a function call needs brackets as does a subquery. You only have one set. However, passing a subquery to a function that has a varchar parameter is fundamentally wrong anyway. @Parado's answer looks pretty good to me. — Dan Bracuk, May 11 '15 at 11:35

score 2 · Answer 1 · answered May 11 '15 at 11:26

2

Maybe like this:

select dbo.myfunc(name) from users

answered May 11 '15 at 11:26

Robert

25,425
8
67
81

will the function be called for each and every record in the table Users? – knkarthick24 May 11 '15 at 11:36
@knkarthick24 Yes it will – Robert May 11 '15 at 11:36
@Parado I getting This ERRO : – Psar Tak May 11 '15 at 12:08
`Cannot find either column "dbo" or the user-defined function or aggregate "dbo.myfunc", or the name is ambiguous.` – Psar Tak May 11 '15 at 12:09
Did you run my query directly without any changes? – Robert May 11 '15 at 12:11
yes , i run your query without any changes . but getting thats error – Psar Tak May 11 '15 at 12:18
@PsarTak sql server doesn't see `dbo.myfunc` function. Are you sure `dbo.myfunc` is on the srever? – Robert May 11 '15 at 12:29
@Parado I run My function with static paramert and work fine . – Psar Tak May 11 '15 at 12:34
@PsarTak Can you put this code with static parameter to the comment? – Robert May 11 '15 at 12:37
select * from dbo.fn_CSVToTable('my value') – Psar Tak May 11 '15 at 12:40
@PsarTak So this is `fn_CSVToTable` not `myfunc` :). Can you run this: `select dbo.fn_CSVToTable('my value')` – Robert May 11 '15 at 12:48
1

@PsarTak It's no problem :) can you run the code which I put in previews comment? – Robert May 11 '15 at 12:53
@Parado I run this :`select dbo.fn_CSVToTable('my value')` And Getting This ERROR:`Cannot find either column "dbo" or the user-defined function or aggregate "dbo.fn_CSVToTable", or the name is ambiguous.` – Psar Tak May 11 '15 at 12:56
@PsarTak I don't know why. It should work. What is your sql-server version? – Robert May 11 '15 at 12:57
@Parado my sql server v is: 2008 – Psar Tak May 11 '15 at 12:59
@PsarTak For this version It should work. [Here](http://stackoverflow.com/questions/8619697/how-to-create-and-call-scalar-function-in-sql-server-2008) is similar solution. – Robert May 11 '15 at 13:13

Gordon Linoff · Answer 2 · 2015-05-11T12:13:23.063

1

Use apply:

Select f.*
from users u cross apply
     dbo.myfunc(u.name) as f;

edited May 11 '15 at 12:13

answered May 11 '15 at 11:24

Gordon Linoff

1,242,037
58
646
786

@PsarTak . . . That names the result from the function. The `f`` is the table alias and the `. . .` are the columns. – Gordon Linoff May 11 '15 at 11:46

How to Call Function with pass select to param

2 Answers2