Cannot access temporary tables from within a function.
Hi,
I need to query a temporary table in a function which is being populated in some stored procedure. The call hierarchy can be understood as follows.
create procedure sp_PopulateTable (
create table #temp1
insert into @tablevariable
select * from fn_DelegateCall ( <some parameters> )
)
/*function called by sp*/
create function fn_DeletgateCall( < parameters > )
(
insert into @somelocalvariable
select * from fn_FinalCall ( <some parameters> )
)
/*function called by function*/
create function fn_DeletgateCall( < parameters > )
(
insert into @sometempvariable
select <columns> from #temp1
)
when i complie this it gives an error
Server: Msg 2772, Level 16, State 1, Procedure fn_FinalCall, Line 60
Cannot access temporary tables from within a function.
Can some one help me to remove this error.
Thanks in advance
I need to query a temporary table in a function which is being populated in some stored procedure. The call hierarchy can be understood as follows.
create procedure sp_PopulateTable (
create table #temp1
insert into @tablevariable
select * from fn_DelegateCall ( <some parameters> )
)
/*function called by sp*/
create function fn_DeletgateCall( < parameters > )
(
insert into @somelocalvariable
select * from fn_FinalCall ( <some parameters> )
)
/*function called by function*/
create function fn_DeletgateCall( < parameters > )
(
insert into @sometempvariable
select <columns> from #temp1
)
when i complie this it gives an error
Server: Msg 2772, Level 16, State 1, Procedure fn_FinalCall, Line 60
Cannot access temporary tables from within a function.
Can some one help me to remove this error.
Thanks in advance
ASKER
No it is not possible to move that logic there
I think what you are trying to do is invalid in sql - having nested INSERT..SELECTS all called the main SELECT statement - this will not work. The logic cannot stay in the functions - why cant the logic be moved into the one stored procedure?
ASKER
The sql server help says that one can use temporary table in functions - " Temporary tables are useful in cases when indexes need to be created explicitly on them, or when the table values need to be visible across multiple stored procedures or functions. ". If this is so then i think there must be some work around for this problem. The logic cannot be moved into the stored procedure because the function in between performs some important logic operation and used up by multiple modules.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
No these nested inserts are working but if i move temp table logic in function then i would have to face some performance issues which i'm trying to remove as the function is being called in loop. in that case i wont get the benefit of introducing temp table in function cause temp table is being populated from main table.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
As I explained the degree to which temporary tables CAN and can't be used within UDFs - AlfaNoMore extended on my answer with a code example - I think this should be a 3 way split between myself, ptjcb and AlfaNoMore
In this case I'd recommend putting the code from in each function, directly into the main stored procedure. I think this would be the best way round this