Temporary Function

[sChapman]

Hi ,

I have a situation where I need to perform calculations on the columns
within a SELECT result set. These calculations can repeat. I have
provided a sample of these calculations, in reality they are much,
much more complicated. The best way I have found to achieve this is by
writing stored functions and calling these as part of SELECT. But the
database on which the SELECT is running, the user will not have
permission to CREATE functions. I can't create the functions as SA
either as these databases are 3rd Party databases and I can't fiddle
with them. I have explored temporary stored functions but these are
not supported. But temporary stored proecedures are.... any idea how I
can create/simulate TEMPORARY STORED FUNCTIONS?


Example of SELECT:

SELECT X,Y, (X+Y) AS [Sum], (X-Y) AS [Diff}, (X*Y) AS [Product]
FROM MyTable

Same thing using Functions

SELECT X,Y,
DBO.MyAdd(X,Y) AS [Sum],
DBO.MyDiff(X,Y) AS [Diff],
DBO.MyMult(X,Y) AS [Prod]
FROM MyTable

[Erland Sommarskog]

Post by sChapman
I have a situation where I need to perform calculations on the columns
within a SELECT result set. These calculations can repeat. I have
provided a sample of these calculations, in reality they are much,
much more complicated. The best way I have found to achieve this is by
writing stored functions and calling these as part of SELECT. But the
database on which the SELECT is running, the user will not have
permission to CREATE functions. I can't create the functions as SA
either as these databases are 3rd Party databases and I can't fiddle
with them. I have explored temporary stored functions but these are
not supported. But temporary stored proecedures are.... any idea how I
can create/simulate TEMPORARY STORED FUNCTIONS?

You cannot create temporary functions.

The nature of these formulas are not clear to me. Are they dynamic in
nature, and change from day to day? Or are they static?

If you can't create functions in the database, because it's a 3rd-party
application, you could put the functions in a separate database on
the side.

[sChapman]

Thanks, the functions are static and don't change every day. I can't
package them into a separate database as I don't have access to the
server on which the database being queried resides. Any ideas how I
can over this problem? I have exhausted all my skills and Google
didn't bring up any answers either.

[Erland Sommarskog]

Post by sChapman
Thanks, the functions are static and don't change every day. I can't
package them into a separate database as I don't have access to the
server on which the database being queried resides. Any ideas how I
can over this problem? I have exhausted all my skills and Google
didn't bring up any answers either.

I'm afraid that I cannot overcome the problem you have of not being
able to create objects on this server. That's a local political issue
that cannot be resolved in a newsgroup. If you cannot overcome that
problem, your code will have to be ugly, or you will need to invent
some program that generates the queries.