On 19 Oct 2001, at 12:56, Helen Borrie wrote:

> At 09:00 AM 19-10-01 +1300, Sandeep wrote:
>
> >No I have a stored procedure and that has following line
> >
> >[ select outputParam from MyStoredProc( Val ) ]
> >
> >repeated more than 50 times. If I replace it with a function call like
> >functionA then the code becomes much eaiser to read.
>
> Sandeep,
>
> Think of UDFs as _external_ functions (which is indeed their "other name") parallel to the vendor-supplied functions that bloat other RDBMSs. They take an input and return a result. They don't go and do stuff inside the database engine unless they were actually built that way. In that case,

the required processing happens right inside the engine - it is an _internal_ function that knows where to find things.

>
> If you try to write an external function that needs to go inside the database and work with its external rules, you are going to bump into problems like "How can my function connect to the database and work within my current transaction context?", to name but one of many.
>
> You are not asking to do the impossible. If one were totally familiar with the engine internals one _could_ write such a function and (because it IS external) build in adequate protection to prevent it corrupting internal structures and memory.
>
> But if you are looking for a "general rule" about UDFs, make it that "External functions should not try to flip back into the database engine". So, if you need to extract a value from your transaction's current view of the data, then your Stored Procedure strategy (or perhaps a scalar query on

a view, as suggested by Ann) is the correct, safe and elegant way to do it.

>
> As far as pretty code is concerned, what is so bad about your Stored Proc call? I'd be more inclined to review the architecture of any procedure that has to include 50 distinct lines each doing exactly the same function OR procedure call...

I cannot give you the source code for stored procedures (company
policy). But thanks anyway.

Regards


Sandeep

Software Developer
CFL
sandeep@...
http://www.cfl.co.nz