User defined function trying to use temp table

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 2005 Forums

Transact-SQL (2005)

User defined function trying to use temp table

Author

Topic

cplusplus
Aged Yak Warrior

567 Posts

Posted - 2009-10-08 : 13:03:59

I am trying to use a temp table within user defined function to calculate some fields getting the totals., i ma getting an error message i cannot use temp tables within user defined functions, is there any other way, Please help.


CREATE FUNCTION [dbo].[UDF_getEFundAllocatedAmount] 
(
	@efundid int,
	@Elementid	int
)
RETURNS bit
AS
BEGIN
DECLARE @AllocatedFundAmt	float

CREATE TABLE #TempEFundAllocated(
efundid	int,
eFundAmt float
);

Insert INTO #TempEFundAllocated(efundid,eFundAmt)
(Select @efundid, isnull(Month1,0) + isnull(month2,0) + isnull(month3,0) from Tbl_RevenueCashFlow where Fundid=@efundid and elementid=@Elementid)


SET @AllocatedFundAmt = 0

SET  @AllocatedFundAmt = (Select isnull(sum(eFundAmt),0) from #TempEFundAllocated)

RETURN @AllocatedFundAmt;

END

Thank you very much for the helpful info.

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2009-10-08 : 13:19:13

You can use youre select thats feeding the temp table as a derived table.

No, you're never too old to Yak'n'Roll if you're too young to die.

cplusplus
Aged Yak Warrior

567 Posts

Posted - 2009-10-08 : 13:31:09

can you please help with some code example in UDF function: that way i can write something.

Really appreceate the info.

Thank you.

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2009-10-08 : 14:49:36

I don't know what you're trying to do but maybe something like this:

CREATE FUNCTION [dbo].[UDF_getEFundAllocatedAmount] 
(
	@efundid int,
	@Elementid	int
)
RETURNS bit
AS
BEGIN
DECLARE @AllocatedFundAmt	float




SET @AllocatedFundAmt = 0

SET  @AllocatedFundAmt = 
(
 Select isnull(sum(eFundAmt),0) from 
 (Select isnull(Month1,0) + isnull(month2,0) + isnull(month3,0) as eFundAmt
  from Tbl_RevenueCashFlow 
  where Fundid=@efundid and elementid=@Elementid
 )dt
)

RETURN @AllocatedFundAmt;

END

No, you're never too old to Yak'n'Roll if you're too young to die.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources