3

I need to pass a local temporary table to a function. But I got this error:

Cannot access temporary tables from within a function.

According to here, I need to create a user defined table type. But I could not get how to pass my table with using this. And my table has millions of row, so just sending it will not be good for me. Are there an way to reach a temp table from a function that I defined?

JollyRoger
  • 737
  • 1
  • 12
  • 38
  • 2
    No. User defined functions cannot access temporary tables. There are a lot of things you can't do with a user defined function. – Zohar Peled Feb 23 '18 at 07:02
  • 1
    maybe you can explain why do you need to access temp table within a function ? – Squirrel Feb 23 '18 at 07:02
  • 1
    You can create "Stored procedure" instead of "User defined function" to access Temp tables. – DatabaseCoder Feb 23 '18 at 07:17
  • Actually I have done it without using function or procedure, thanks a lot. But I need to know that how can we use a table that is combination of two table(I mean join) to do something else: For example "select * from a full join b" Here I need to use the table that is occured after join in the where clause inside of a select. Like this: "select x from a join b on a.id = b.id where (x > (select min(x) from z))", here z is the result of join. How can I take this z? @Squirrel – JollyRoger Feb 23 '18 at 07:27
  • For that case, I will use CTE. eg. ;WITH z as (select * from a full join b) select x from a join b on a.id = b.id where (x > (select min(x) from z)) – DatabaseCoder Feb 23 '18 at 07:37

0 Answers0