I have created a table-valued function like:
CREATE FUNCTION [dbo].[fnGet]
RETURNS @RtnValue table
INSERT INTO @RtnValue
) AS #C
ON A.column = #C.column
WHERE column = @List
However, I find that the #C won't be dropped in the tempdb after the function execution. It can't be remove unless I restarted the server.
If I added:
Drop Table #C
in the function, there will be an error message saying "Cannot access temporary tables from within a function".
Please advice/suggest how can I remove the temp table after the function execution. I am using MSSQL 2005.
P.S. I know that I can use table variable instead, but I don't want to do this since it will involve lots of changes for me. I wish I can just drop the temp table by script or "manually".
View Complete Post