Msg 2772, Level 16, State 1, Procedure MyDate, Line 8
Cannot access temporary tables from within a function.
This error message appears when you try to access a temporary table from within a user-defined function.
The T-SQL statement can be parsed, but causes the error at runtime.
Errors of the Severity Level 16 are generated by the user and can be fixed by the SQL Server user. The statement cannot be executed this way. You cannot access a temporary table from within a function.
This error message was introduce with SQL Server 2000.
IF OBJECT_ID(‘tempdb..#t’) > 0
DROP TABLE #t
CREATE TABLE #t
id INT NOT NULL
IF OBJECT_ID(‘dbo.MyInt’, ‘function’) > 0
DROP FUNCTION dbo.MyDate
CREATE FUNCTION dbo.MyDate(@i AS INT)
WITH EXECUTE AS CALLER
RETURN (SELECT id
WHERE id = @i)
In the above example we try to access the temporary table #t from within the user-defined function MyInt. This raises the error.