table to store function name

Posted on 2012-09-17
Last Modified: 2012-09-18

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.


Question by:Graham_Forbes
    LVL 25

    Expert Comment

    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.

    Author Comment

    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
    LVL 5

    Expert Comment

    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?

    Author Comment

    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

    LVL 25

    Expert Comment

    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.
    LVL 25

    Expert Comment

    Or, the table lookup for version could be in the wrapper function.
    LVL 5

    Expert Comment

    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
    DECLARE @Output 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.
    LVL 25

    Accepted Solution

    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


    Author Closing Comment

    thanks for this, like this solution as it also gives a history of the changes to the formula


    Featured Post

    Maximize Your Threat Intelligence Reporting

    Reporting is one of the most important and least talked about aspects of a world-class threat intelligence program. Here’s how to do it right.

    Join & Write a Comment

    Recently, when I was asked to create a new SQL 2005 cluster, Microsoft released a new service pack for MS SQL 2005 what is Service Pack 3. When I finished the installation of MS SQL 2005 I found myself troubled why the installation of SP3 failed …
    In this article I will describe the Copy Database Wizard method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
    Hi everyone! This is Experts Exchange customer support.  This quick video will show you how to change your primary email address.  If you have any questions, then please Write a Comment below!
    This video gives you a great overview about bandwidth monitoring with SNMP and WMI with our network monitoring solution PRTG Network Monitor ( If you're looking for how to monitor bandwidth using netflow or packet s…

    755 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    19 Experts available now in Live!

    Get 1:1 Help Now