Function to call function by name given as parameter

I want to write function to call another function which name is
parameter to first function. Other parameters should be passed to
called function.
If I call it function('f1',10) it should call f1(10). If I call it
function('f2',5) it should call f2(5).

So far i tried something like

CREATE FUNCTION [dbo].[func] (@.f varchar(50),@.m money)
RETURNS varchar(50) AS
BEGIN
return(select 'dbo.'+@.f+'('+convert(varchar(50),@.m)+')')
END

When I call it select dbo.formuła('f_test',1000) it returns
'select f_test(1000)', but not value of f_test(1000).

What's wrong?

MariuszMariusz (vd06@.o2.pl) writes:
> I want to write function to call another function which name is
> parameter to first function. Other parameters should be passed to
> called function.
> If I call it function('f1',10) it should call f1(10). If I call it
> function('f2',5) it should call f2(5).
> So far i tried something like
> CREATE FUNCTION [dbo].[func] (@.f varchar(50),@.m money)
> RETURNS varchar(50) AS
> BEGIN
> return(select 'dbo.'+@.f+'('+convert(varchar(50),@.m)+')')
> END
> When I call it select dbo.formuła('f_test',1000) it returns
> 'select f_test(1000)', but not value of f_test(1000).
> What's wrong?

Nothing. Or everything. Just take a step back, and put yourself in
the position of SQL Server. You tell SQL Server to evaluate a string
expression. How on Earth should SQL Server see that the result of this
expression is its turn also an expression that should be evaluated?

Had you been in a stored procedure, you could have used dynamic SQL. Now
you are in a function, and the only way to do this is:

IF @.f = 'that_func'
RETURN (dbo.that_func(@.f))
ELSE @.f = 'this_func'
RETURN (dbo.that_func(@.f))
etc

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||> Had you been in a stored procedure, you could have used dynamic SQL. Now
> you are in a function, and the only way to do this is:
> IF @.f = 'that_func'
> RETURN (dbo.that_func(@.f))
> ELSE @.f = 'this_func'
> RETURN (dbo.that_func(@.f))
> etc
But I want to call this_func or that_func, or maybe a few other
functions without a need to modify wrapper function. Somehow I managed
to write stored procedure which does what I want:

CREATE PROCEDURE [dbo].[f] @.funkcja varchar(50), @.arg1 varchar(50),
@.koszt money OUTPUT
AS
BEGIN
declare @.cmd nvarchar(50)
declare @.par nvarchar(50)
set @.cmd=N'set @.koszt='+@.funkcja+'(@.arg)'
set @.par=N'@.koszt money output, @.arg varchar(50)'
execute sp_executesql @.cmd, @.par, @.koszt output, @.arg=@.arg1
END

Now I have onother problem: SPs cannot be used inside functions.
Only functions and extended SPs. Can I write extended SP to execute
SPs from functions?

Mariusz|||Mariusz (vd06@.o2.pl) writes:
> But I want to call this_func or that_func, or maybe a few other
> functions without a need to modify wrapper function. Somehow I managed
> to write stored procedure which does what I want:
> CREATE PROCEDURE [dbo].[f] @.funkcja varchar(50), @.arg1 varchar(50),
> @.koszt money OUTPUT
> AS
> BEGIN
> declare @.cmd nvarchar(50)
> declare @.par nvarchar(50)
> set @.cmd=N'set @.koszt='+@.funkcja+'(@.arg)'
> set @.par=N'@.koszt money output, @.arg varchar(50)'
> execute sp_executesql @.cmd, @.par, @.koszt output, @.arg=@.arg1
> END
> Now I have onother problem: SPs cannot be used inside functions.
> Only functions and extended SPs. Can I write extended SP to execute
> SPs from functions?

Yes, but in such case why call the stored procedure? Why not call the
function from the external stored procedure directly if you really want
to take this road. And I think it would be a very very bad road to take.
There are tons of reasons why you should not go there.

It might be that you already have external stored procedures in the
system (for better reasons than this one), but if you have not, you
have created a deplyoment problem. There is one more component that should
be deployed in production.

And extended stored procedures always incur a risk. An access violation
does not only crash your stored procedure - the whole SQL Server process
is blown away.

Furthermore, apparently this is a scalar function. If you say:

SELECT dbo.f(@.funkcja, @.arg1) FROM tbl

and you call an extended stored proc for each row in tbl, how effeciently
do you that will be?

Rewrite your functions to stored procedures that work on a temptable or
a spid-keyed table where it receives input parameters and return data. You
need a dispatch procedure, as you can say:

EXEC @.sp @.arg1

Where @.sp is the name of your procedure. (The above works for scalar-
values UDF:s also, by the way.)

For more information about sharing data over temp-tables, please see
http://www.sommarskog.se/share_data.html#temptables.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp