levsha_z (9/4/2008)

---

I'm trying to create a function but getting an error message about the use of EXEC within a function.

I need this function to use it's output result as one of the items on an outside SELECT statement.

I need to use dynamic SQL within the function because a number of the fields is dynamic, and I need to summirize values from all 'fee' fields for each record in the table separately.

In other words, if I have 'fee1' = 5, 'fee2' = 3, 'fee3' = 4

in the first row of the table, the result I need is 5 + 3 + 4 = 12.

The same goes for each row in the table.

Here is the table:

create table mytable (feeid int, fee1 money, fee2 money, fee3 money)

insert mytable values (1, 32, 11, 24)

insert mytable values (2, 12, 25, 44)

insert mytable values (3, 16, 14, 42)

This is the function I'm trying to create:

CREATE FUNCTION [dbo].[GetWPSum](@feeid int)

RETURNS money

AS

BEGIN

declare @i int,

@err int,

@cols int,

@one_fee_select varchar(200),

@onefee money,

@totalfee money

declare @mytable table (fee money)

select @totalfee = 0

set @i = 1

set @err = 0

select @cols = count(*) from syscolumns

where name like 'fee%' and id = (select id from sysobjects

where name = 'mytable')

while @i <= @cols

begin

set @one_fee_select = 'select isnull(fee' + cast(@i as varchar) + ', 0) from mytable'

insert into @mytable exec(@one_fee_select)

select @onefee = fee from @mytable

select @totalfee = @totalfee + @onefee

delete @mytable

set @i = @i + 1

set @err = @@error

select @onefee=0

end

RETURN @totalfee

END

This is the error message I'm getting:

Msg 443, Level 16, State 14, Procedure GetWPSum, Line 31

Invalid use of side-effecting or time-dependent operator in 'INSERT EXEC' within a function.

This is an outside SELECT, for which I need a result from the function:

select feeid, dbo.getWPSum(feeid)

from mytable

Please advise!