Learn how to a build a cloud-first strategyRegister Now


table to store function name

Posted on 2012-09-17
Medium Priority
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
  • 4
  • 3
  • 2
LVL 25

Expert Comment

ID: 38408348
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

ID: 38408393
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

Expert Comment

ID: 38408416
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?
 [eBook] Windows Nano Server

Download this FREE eBook and learn all you need to get started with Windows Nano Server, including deployment options, remote management
and troubleshooting tips and tricks


Author Comment

ID: 38408440
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

ID: 38408472
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

ID: 38408476
Or, the table lookup for version could be in the wrapper function.

Expert Comment

ID: 38408492
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.
LVL 25

Accepted Solution

lwadwell earned 2000 total points
ID: 38408507
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

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


Featured Post

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

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 …
So every once in a while at work I am asked to export data from one table and insert it into another on a different server.  I hate doing this.  There's so many different tables and data types.  Some column data needs quoted and some doesn't.  What …
Look below the covers at a subform control , and the form that is inside it. Explore properties and see how easy it is to aggregate, get statistics, and synchronize results for your data. A Microsoft Access subform is used to show relevant calcul…
As many of you are aware about Scanpst.exe utility which is owned by Microsoft itself to repair inaccessible or damaged PST files, but the question is do you really think Scanpst.exe is capable to repair all sorts of PST related corruption issues?

810 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