February 19, 2003 at 4:53 am
Hi
I'm looking for a solution to a problem surrounding u-d functions
I want to be able to call a function within another Main function. I want to read the name of the function to be called from a string field in a table.
The Main function builds up a string of values. I want to add onto this string the result of the function, if one of these values has a function name entry in the source table.
The Main function will be called in an SP select statement, and will return a string result, processed for every row returned.
Any thoughts on how I can get this function name out and call it within this (or another) function? or any other methods that would yield the same result?
My obvious stumbling block is that you cannot use dynamic sql in functions and the fact the functions need to be 'deterministic', which may rule this out all together. I'm not convinced I will be able to do this, but thought I would ask, and if anyone has any ideas, it would be most appreciated.
Many Thanks
Code example to elaborate on what I mean:
Create Table Test
(
ID int,
Value varchar(50),
FunctionName varchar(255)
)
create function funTest(@ID int)
return varchar(500)
as
begin
declare @s-2 varchar(5000)
set @s-2 = ''
select @s-2 = @s-2 + @Value + '|' +
case
when FunctionName is not nulll
then /*Call FunctionName here*/ dbo."FunctionName"(ID)
else ''
end
from Test
where Id = @ID
Select *, funTest(Id)
from Test2
inner join Test....
February 19, 2003 at 4:59 am
Unfortunately as you cannot parameterize the name of an object such as a function for it to use. Thus you need dynamic SQL. However if you have a small number of possibilities you could use a if...else statement to run the specific function.
February 19, 2003 at 5:23 am
Thanks for the quick response and the suggestion. Using your suggestion has improved the method I was looking at, I can now still keep the function name in the tables, and refer to it in an if else (when then) sense. Cheers.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply