By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
435,426 Members | 3,118 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 435,426 IT Pros & Developers. It's quick & easy.

Function to call function by name given as parameter

P: n/a
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?

Mariusz
Jul 20 '05 #1
Share this Question
Share on Google+
3 Replies


P: n/a
Mariusz (vd**@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, es****@sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp
Jul 20 '05 #2

P: n/a
> 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
Jul 20 '05 #3

P: n/a
Mariusz (vd**@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, es****@sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp
Jul 20 '05 #4

This discussion thread is closed

Replies have been disabled for this discussion.