Unable to create temporary tables inside functions
-
I Can't create temporary tables inside functions
I don't know why
when I write the following
Create FUNCTION GetNumberOfPostsInForum(@ForumId int)
returns int
as
begin
declare @NumberOfPostsInForum int;
select * into #TemporaryTopics from dbo.GetTopicsOfForum(@ForumId);
select @NumberOfPostsInForum=count(*) from #TemporaryTopics,Posts WHERE Posts.TopicId=#TemporaryTopics.TopicId
drop table #TemporaryTopics;
return @NumberOfPostsInForum;
end
where dbo.GetTopicsOfForum(@ForumId) is a function that returns a table
I receive the following error
Cannot access temporary tables from within a function.
Why I am not able to create temp tables in the functions?
and is there alternative methods!!
thanks
-
The temp table is only your first problem.
I don't think you can call function from functions either.
Use derived table.
Select count(*)
from (Select field
From mytable
where Foo = 'Somevalue'
....) As T
-
You can call functions from within functions provided they are "determinate"... it is, in my humble opinion, kind of a bad thing to make one function dependent on another.
So far as the temp table thing in a UDF goes, the error message you got say's it all... it can't be done.
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
Why you need temp table at all?
Create FUNCTION GetNumberOfPostsInForum(@ForumId int)
returns int
as
begin
declare @NumberOfPostsInForum int
select @NumberOfPostsInForum=count(*)
from dbo.GetTopicsOfForum(@ForumId) T
INNER JOIN Posts ON Posts.TopicId= T.TopicId
return @NumberOfPostsInForum;
end
_____________
Code for TallyGenerator -
DOH! I didn't even look at the code...
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
You can use Table variables....
-
Amit,
That might be what you consider to be a temporary table but it's not a "Temp" table like what the user was requesting. Temp tables live in TempDB and the first character or two (# or ##) names determine where they live and whether they are local or global as well as when they will be automatically dropped.
Also, instead of using a hardcoded login and password in the clear text of the code, you could simply use the -T (trusted connection) parameter.
Also, you must have SA permissions or have a proxy setup in order to be able to use xp_CmdShell. Most DBA's frown on the practice of giving folks those kinds of permissions.
Clever bit of code, though. Thanks for posting it. I am curious,... why do you capture the rowcount in AB.TXT?
J Wright... You are absolutely correct but like Serqiy suggested, I don't think a temporary structure is even necessary here. One other thing to be aware of (just in case folks don't know)... table variables do not and cannot be made to use statistics. Make a big one and the code will really slow down.
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
Hi,
I have a function in which I have to retrieve values from 3 different tables based on some criteria. Because of the joins with one particular table which has over a million rows, the turn-around-time of the function is pretty slow. I thought I could apply the criteria related to that particular table separately and extract the data, store it in a temp./temporary table and use that table alongwith the other tables. Due to the error in focus on this thread, I am rendered helpless. Can you guys throw some light and help me?
-
Can you post the function, the definition of the tables and the indexes on those tables please.
http://www.sqlservercentral.com/articles/Best+Practices/61537/
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverabilityWe walk in the dark places no others will enter
We stand on the bridge and no one may pass
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply