table to store function name


I have a stored procedure beiing called from the web site. the stored procedure name is stored in a parameter in the web.config file. within the stored procedure I want to call a function to evaluate a formula. I then need to be able to change the function name if the formula changes. ideally I would like to read the function name from a SQL table.


Who is Participating?
lwadwellConnect With a Mentor Commented:
Example of wrapper function would be along the lines of below.  Newest version should go on top for minor performance reasons.

CREATE FUNCTION xyz_function (@version int, @param1 int, @parame2 date)
RETURNS @result int
    IF @version = 3
        SET @result = xyz_function_v3(@param1,@param2)
    ELSE IF @version = 2
        SET @result = xyz_function_v2(@param1,@param2)
        SET @result = xyz_function_v1(@param1,@param2)

Open in new window

Excuse me for asking ... but why?  This seems to me like an odd design choice I am wondering if there are better alternatives based on the need.
Graham_ForbesAuthor Commented:
the project is a pricing configurator. the web site accepts lots of parameters and then applies business rules to work out, for exampe how many servers we need. the business rules will change, but ideally would like to be able to control which rule is used.

for example

number.of.servers= dbo.getnumberofservers_v1(@numberusers,@servertype,@resilence)

the function would then be something like:

if servertype = 'DL100'

      RESULT=@numberusers/ 100
IF @resilence THEN RESULT=RESULT + 1


thanks for any advise
Cloud Class® Course: Amazon Web Services - Basic

Are you thinking about creating an Amazon Web Services account for your business? Not sure where to start? In this course you’ll get an overview of the history of AWS and take a tour of their user interface.

Hi Graham,

do you mean, your business rules change on the fly?

and so you need your proc to be able to create new functions when it happens or you will have all functions created in advanced but just want the proc to choose the right one based on the business rules?
Graham_ForbesAuthor Commented:
the rules will not change on the fly, I will have multiple functions eg

etc and then have a SQL table which would store which function should be called.

I guess I could acheieve the same result with changing the stored procedure with ever change, but with lots of functions wondered if could be done in a better way

What I would think about perhaps ... is masking the functions a little.  This is me thinking out loud a bit.
I would perhaps consider changing the function to include an extra parameter ... version number.  Hence call a function, eg. dbo.getnumberofservers(), in the procedure with the version number being passed, the function then calls the correct version.  So if a new function version is introduced ... create the new function and modify the wrapper parent function.
Plus ... use a configuration table that stores the function name and current version.  The procedure can use this table to determine the version value to pass in the call.
This allows for backward compatibility, if function goes awry - update the table to back down to a previous version.  It limits the places where code is modified and allows the procedure to have a set function name ... the version number is the variable from a table.

It is a suggestion ... ignore me if you like.
Or, the table lookup for version could be in the wrapper function.
Oh that's the case.

Sure, you can create a mapping table. But to fetch and use the appropriate function inside the proc, you will have to use dynamic sql execution.

so for instance, assuming that your table that stores the functions has the following structure,

FuncId     |      FuncName                                  |   CaseId
1                dbo.getnumberofservers_v1                  A
2                dbo.getnumberofservers_v2                  B

Now in the proc, you decide case is to be processed and fetch the fucntionName in  a variable

declare @funcName varchar(200)

select @funcName = funcName from mappingTable Where caseId = 'A'

Now that you have zeroed on the function to use, we have to create the sql that should execute that function and return the value we are looking at

DECLARE @ParmDefinition NVARCHAR(500)
DECLARE @Examplevariable INT
SET @SQLString = N'select @OutputOUT =  ' + @funcName + '(@Input)'
SET @ParmDefinition = N'@Input INT,
                        @OutPutOUT INT OUTPUT'
EXECUTE sp_executesql
@Input = @ExampleVariable,
@OutPutOUT = @Output OUTPUT
SELECT @Output

You can get the output from the function in @OutPut variable

and then can use this further in the procedure

The assumption is that the function takes similar number of parameters.

let me know if it makes sense

Another approach could be to create a seperate procedure which you call from your main proce and pass the required parameters for the function calls. This new procedure can encapsulate the decision logic for calling the desired function and can return back the results to the calling proc.

Here you will not need the table.
Graham_ForbesAuthor Commented:
thanks for this, like this solution as it also gives a history of the changes to the formula

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.