SQL : Cannot access temporary tables from within a function.

kkran 831 Reputation points

Hi Team - Could you please help fix the below:

These are the errors:
Cannot access temporary tables from within a function.

Msg 137, Level 15, State 2, Procedure fn_SetContractLine_ProcessingFlag, Line 99 [Batch Start Line 271]

Must declare the scalar variable "@returnValue".

SQL Server
SQL Server
A family of Microsoft relational database management and analysis systems for e-commerce, line-of-business, and data warehousing solutions.
12,680 questions
{count} votes

2 answers

Sort by: Most helpful
  1. Erland Sommarskog 100.9K Reputation points MVP

    The error messages speak for themselves. You cannot use tempoary tables in function, so whatever your plan was, it is back to the drawingboard.

    Since we don't really know what your original plan was, we cannot really help with the small amout of detail you have shared.

    And as for the other error, well my not declare the variable?

    0 comments No comments

  2. PercyTang-MSFT 12,426 Reputation points Microsoft Vendor

    Hi @kkran

    As Erland says, the error messages are already obvious. You can improve your code based on error messages.

    For the error that cannot access temporary tables from within a function, you can actually try an alternative method.

    You can try using table variables.

    You can refer to this post to try the methods provided inside.


    Best regards,

    Percy Tang

    If the answer is the right solution, please click "Accept Answer". If you have extra questions about this answer, please click "Comment".

    Note: Please follow the steps in our Documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

    0 comments No comments