Is it possible to use temp table within UDF

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

Is it possible to use temp table within UDF

Author

Topic

cplusplus
Aged Yak Warrior

567 Posts

Posted - 2011-03-27 : 06:30:55

I am creating a userdefined function.

Within it would like to create a temp table, when i try to save my UDF i get a message saying temp table not possible within UDF.

CREATE FUNCTION [dbo].[UDF_PctElCmplt]
(
-- Add the parameters for the function here
@Order_id int
)
RETURNS Float
AS
BEGIN
DECLARE @Output Float

SET @Output = 0

create table #Summary_set (
ID int identity(1,1) not null,
Order_id int,
taskid int,
PctComplete int,
TskEV float)

Insert into #Summary_set (element_id,taskid,PctComplete,TskEV) select my fields .........

RETURN @Output;
END

Thank you very much for the helpful info.

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2011-03-27 : 07:26:14

This is a restriction (among many) of UDFs. Not only can you create a temporary table, you cannot even access one even if it is in scope.

GilaMonster
Master Smack Fu Yak Hacker

4507 Posts

Posted - 2011-03-27 : 07:34:41

I believe you can create a table variable, but not a temp table

--
Gail Shaw
SQL Server MVP

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources