Local and global temporary tables play a vital role in the SQL Server scripting. We generally use it to store temporary values for further manipulation. But unfortunately, you cannot use it inside the user defined function.
PROBLEM :
If you use temporary tables in the function, you will get the below error message.
Msg 2772, Level 16, State 1, Procedure ufnGetEmployeeInformation, Line 18
Cannot access temporary tables from within a function.
So, what is the solution?
SOLUTION :
To resolve this, you need to use table variables instead of temporary tables. You can easily accommodate it in the user defined function for further manipulation.
Given below is a sample.
--This script is compatible with SQL Server 2005 and above. --DROP FUNCTION [dbo].[ufnGetEmployeeInformation] --GO USE AdventureWorks2012 GO CREATE FUNCTION [dbo].[ufnGetEmployeeInformation] (@BusinessEntityID int) RETURNS @EmployeeInformation TABLE ( [BusinessEntityID] int, [FirstName] [nvarchar](50) NULL, [LastName] [nvarchar](50) NULL, [JobTitle] [nvarchar](50) NULL, [City] [nvarchar](30) NULL, [Return Time] [datetime] NULL ) AS BEGIN INSERT INTO @EmployeeInformation SELECT [BusinessEntityID], [FirstName], [LastName], [JobTitle], [City], getdate() FROM [HumanResources].[vEmployee] WHERE BusinessEntityID=@BusinessEntityID RETURN; END GO
EXAMPLE :
--This script is compatible with SQL Server 2005 and above. USE AdventureWorks2012 GO SELECT * FROM [dbo].[ufnGetEmployeeInformation](124) --OUTPUT
SQL Server Portal 