Feeds:
Posts
Comments

Posts Tagged ‘temporary tables’

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

use temporary table in function.1.1

Read Full Post »