?
Solved

Cannot access temporary tables from within a function.

Posted on 2005-03-02
12
Medium Priority
?
11,230 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
12 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
How Blockchain Is Impacting Every Industry

Blockchain expert Alex Tapscott talks to Acronis VP Frank Jablonski about this revolutionary technology and how it's making inroads into other industries and facets of everyday life.

 
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

Moving data to the cloud? Find out if you’re ready

Before moving to the cloud, it is important to carefully define your db needs, plan for the migration & understand prod. environment. This wp explains how to define what you need from a cloud provider, plan for the migration & what putting a cloud solution into practice entails.

Question has a verified solution.

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

For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
A Stored Procedure in Microsoft SQL Server is a powerful feature that it can be used to execute the Data Manipulation Language (DML) or Data Definition Language (DDL). Depending on business requirements, a single Stored Procedure can return differe…
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.
Suggested Courses

777 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