Run SP from UDF?

Hi,
Is it possible to run a Stored Proc from a UDF?
If it is possible, how do I store the resultset that the SP produces? The
code I have does not work, as I don't think you can use the EXEC keyword in
a
UDF but here it is anyway:
DECLARE @.tempMeterData TABLE (
RoundedTimestamp smalldatetime NOT NULL,
[Value] float NULL)
INSERT INTO @.tempMeterData EXEC sp_MySP @.ID
Any help on how I could achieve this would be gratefully received.
Thanks,
SteveAs you said, you can not call a stored procedure (you can call exteded ones)
inside a udf.
What are you trying to accomplish?
AMB
"SteveN" wrote:

> Hi,
> Is it possible to run a Stored Proc from a UDF?
> If it is possible, how do I store the resultset that the SP produces? The
> code I have does not work, as I don't think you can use the EXEC keyword i
n a
> UDF but here it is anyway:
> DECLARE @.tempMeterData TABLE (
> RoundedTimestamp smalldatetime NOT NULL,
> [Value] float NULL)
> INSERT INTO @.tempMeterData EXEC sp_MySP @.ID
> Any help on how I could achieve this would be gratefully received.
> Thanks,
> Steve
>|||I am updating an old UDF which is used widely in an ASP.NET application. Th
e
UDF returns a table, so it probably should be an SP, but that is a different
matter.
Data the UDF needs to return now (depending on parameters) is calculated in
an existing SP, so I want to be able to get the return data from that SP to
use in the existing UDF.
Any ideas on what I can do?
Thanks,
Steve
"Alejandro Mesa" wrote:
> As you said, you can not call a stored procedure (you can call exteded one
s)
> inside a udf.
> What are you trying to accomplish?
>
> AMB
> "SteveN" wrote:
>|||Replace the function with an SP or move the logic from the existing SP
into the function.
David Portas
SQL Server MVP
--|||What is the reason of returning this resultset using a udf an not calling th
e
sp directly?
Are you planning to do extra manipulation on the result of the sp?
How complicated is the process inside the sp?
AMB
"Steve Norman" wrote:
> I am updating an old UDF which is used widely in an ASP.NET application.
The
> UDF returns a table, so it probably should be an SP, but that is a differe
nt
> matter.
> Data the UDF needs to return now (depending on parameters) is calculated i
n
> an existing SP, so I want to be able to get the return data from that SP t
o
> use in the existing UDF.
> Any ideas on what I can do?
> Thanks,
> Steve
> "Alejandro Mesa" wrote:
>|||Further manipulation on the resultset will be done in the UDF before it is
returned as a table, the SP that is called is 367 lines and is very
complicated indeed.
I am trying to find an alternative to re-writing the UDF as a SP as there
are other UDF's that use this UDF as the core to their data manupulation, so
I would also need to re-write all of these as SPs.
If that is the only answer than that is what I will have to do, but I was
trying to save a couple of days of re-writing and testing by simply getting
data from an exisitng SP in a couple of lines of code.
Steve
"Alejandro Mesa" wrote:
> What is the reason of returning this resultset using a udf an not calling
the
> sp directly?
> Are you planning to do extra manipulation on the result of the sp?
> How complicated is the process inside the sp?
>
> AMB
>
> "Steve Norman" wrote:
>|||Steve,
Your working with .Net,
Run you sp and output the parameters then run your function from .net with
newly acquired params ?
"Steve Norman" wrote:
> Further manipulation on the resultset will be done in the UDF before it is
> returned as a table, the SP that is called is 367 lines and is very
> complicated indeed.
> I am trying to find an alternative to re-writing the UDF as a SP as there
> are other UDF's that use this UDF as the core to their data manupulation,
so
> I would also need to re-write all of these as SPs.
> If that is the only answer than that is what I will have to do, but I was
> trying to save a couple of days of re-writing and testing by simply gettin
g
> data from an exisitng SP in a couple of lines of code.
> Steve
> "Alejandro Mesa" wrote:
>