SQL : Cannot access temporary tables from within a function.

kkran 831 Reputation points
2023-03-13T21:13:24.94+00:00

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
    2023-03-13T22:00:47.64+00:00

    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
    2023-03-14T01:48:36.1533333+00:00

    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.

    https://stackoverflow.com/questions/9561626/cannot-access-temporary-tables-from-within-a-function

    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