Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

Cannot access temporary tables from within a function.

Posted on 2005-03-02
12
Medium Priority
?
11,515 Views
Last Modified: 2011-09-20
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
0
Comment
Question by:answer_me
9 Comments
 
LVL 23

Expert Comment

by:adathelad
ID: 13437560
As the error message says - you cannot reference a temporary table from within a user defined function.

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
0
 
LVL 10

Author Comment

by:answer_me
ID: 13437602
No it is not possible to move that logic there
0
 
LVL 23

Expert Comment

by:adathelad
ID: 13437678
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?

0
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 
LVL 10

Author Comment

by:answer_me
ID: 13437718
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.
0
 
LVL 23

Accepted Solution

by:
adathelad earned 172 total points
ID: 13437764
Yes, you can use temp tables in functions - you can create a temp table/variable and manipulate it within the function and even return a TABLE variable, but you cannot reference external temp tables - it's all a matter of scope. The only other work around would be to use a physical table, but still then I think you'd get errors  with the nested INSERTS you are doing all called from the main SELECT statement in the SP.
0
 
LVL 10

Author Comment

by:answer_me
ID: 13437805
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.
0
 
LVL 27

Assisted Solution

by:ptjcb
ptjcb earned 164 total points
ID: 13439121
http://www.informit.com/articles/article.asp?p=31724&seqNum=2

Cannot Use Temporary Tables

UDFs cannot make use of temporary tables. As an alternative, you are allowed to use table variables within a UDF. Recall however, that temporary tables are somewhat more flexible than table variables. The latter cannot have indexes (other than a primary and unique key); nor can a table variable be populated with an output of a stored procedure.

As adathelad says, it is a matter of scope.

0
 
LVL 9

Assisted Solution

by:AlfaNoMore
AlfaNoMore earned 164 total points
ID: 13440463
Looks like you should be able to do what you want to do? Your UDF's will return a TABLE.

Example straight out of BOL.

CREATE FUNCTION LargeOrderShippers ( @FreightParm money )
RETURNS @OrderShipperTab TABLE
   (
    ShipperID     int,
    ShipperName   nvarchar(80),
    OrderID       int,
    ShippedDate   datetime,
    Freight       money
   )
AS
BEGIN
   INSERT @OrderShipperTab
        SELECT S.ShipperID, S.CompanyName,
               O.OrderID, O.ShippedDate, O.Freight
        FROM Shippers AS S INNER JOIN Orders AS O
              ON S.ShipperID = O.ShipVia
        WHERE O.Freight > @FreightParm
   RETURN
END
0
 
LVL 23

Expert Comment

by:adathelad
ID: 13724801
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

0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

Windocks is an independent port of Docker's open source to Windows.   This article introduces the use of SQL Server in containers, with integrated support of SQL Server database cloning.
This shares a stored procedure to retrieve permissions for a given user on the current database or across all databases on a server.
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.
Viewers will learn how the fundamental information of how to create a table.
Suggested Courses

564 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