473,903 Members | 4,817 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

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',1 0) 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+'('+c onvert(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
3 24019
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',1 0) 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+'('+c onvert(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****@sommarsk og.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp
Jul 20 '05 #2
> 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='+@funkc ja+'(@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
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='+@funkc ja+'(@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****@sommarsk og.se

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

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

9
4971
by: Penn Markham | last post by:
Hello all, I am writing a script where I need to use the system() function to call htpasswd. I can do this just fine on the command line...works great (see attached file, test.php). When my webserver runs that part of the script (see attached file, snippet.php), though, it doesn't go through. I don't get an error message or anything...it just returns a "1" (whereas it should return a "0") as far as I can tell. I have read the PHP...
9
3704
by: Derek Hart | last post by:
I wish to execute code from a string. The string will have a function name, which will return a string: Dim a as string a = "MyFunctionName(param1, param2)" I have seen a ton of people discuss how reflection does this, but I cannot find the syntax to do this. I have tried several code example off of gotdotnet and other articles. Can somebody please show me the code to do this?
3
14972
by: domeceo | last post by:
can anyone tell me why I cannot pass values in a setTimeout function whenever I use this function it says "menu is undefined" after th alert. function imgOff(menu, num) { if (document.images) { document.images.src = eval("mt" +menu+ ".src") } alert("imgOff_hidemenu"); hideMenu=setTimeout('Hide(menu,num)',500);
1
3355
by: jimfortune | last post by:
From: http://groups-beta.google.com/group/comp.databases.ms-access/msg/769e67e3d0f97a90?hl=en& Errata: 19 solar years = 2939.6018 days should be 19 solar years = 6939.6018 days Easter Function explanation Part II
12
9554
by: Newbie | last post by:
how can i call an oracle function to get data without using a select statement or stored procedures? given a project_no, i need to call the function: ops$sqltime.pa_new_job_no_fn which will return the next job_no thanks in advance.
12
5815
by: leaf | last post by:
Hi, How to call function at runtime, based on a struct that contains the information for the function call: struct func_to_call { int function_id; // function id to call unsigned int nparams; // number of parameters unsigned long* parameter; // the parameter(s) to pass }
28
4362
by: Larax | last post by:
Best explanation of my question will be an example, look below at this simple function: function SetEventHandler(element) { // some operations on element element.onclick = function(event) {
4
2391
by: simon | last post by:
hi, I would like to separate my javascript completely from my xhtml. in the end there should be only <script type="text/javascript" src="javalib.js"></script> in the head-tag to my javascript. Because I want to use some ajax-requests and other javascript-functions on my xhtml, I need to dynamically add event handlers to any possible dom-elements. All solutions I found so fare are for specific, pre-known
10
3682
by: Richard Heathfield | last post by:
Stephen Sprunk said: <snip> Almost. A function name *is* a pointer-to-function. You can do two things with it - copy it (assign its value to an object of function pointer type, with a cast if necessary but preferably to one of the /right/ function pointer
0
9997
marktang
by: marktang | last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, weíll explore What is ONU, What Is Router, ONU & Routerís main usage, and What is the difference between ONU and Router. Letís take a closer look ! Part I. Meaning of...
0
10872
jinu1996
by: jinu1996 | last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that captivates audiences and drives business growth. The Art of Business Website Design Your website is...
1
10981
by: Hystou | last post by:
Overview: Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For most users, this new feature is actually very convenient. If you want to control the update process,...
0
10499
tracyyun
by: tracyyun | last post by:
Dear forum friends, With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the choice of these technologies. I'm particularly interested in Zigbee because I've heard it does some...
0
7205
by: conductexam | last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one. At the time of converting from word file to html my equations which are in the word document file was convert into image. Globals.ThisAddIn.Application.ActiveDocument.Select();...
0
5893
by: TSSRALBI | last post by:
Hello I'm a network technician in training and I need your help. I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs. The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols. I succeeded, with both firewalls in the same network. But I'm wondering if it's possible to do the same thing, with 2 Pfsense firewalls...
0
6085
by: adsilva | last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
1
4725
by: 6302768590 | last post by:
Hai team i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
2
4307
muto222
by: muto222 | last post by:
How can i add a mobile payment intergratation into php mysql website.

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.