September 4, 2008 at 8:14 am
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!
September 4, 2008 at 8:59 am
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!
* Dynamic SQL is not supported in UDF.
* You should re-think that design.
* Noel
September 4, 2008 at 9:17 am
September 4, 2008 at 9:44 am
You might be able to accomplish what you need in a CLR UDF. Will be a whole different animal, but might give you what you need.
Of course, the whole problem is caused by serious violations of normal form. Is it possible, at some point in the future, to revamp the database into a more standard format?
Another possibility, keeping it in the realm of T-SQL, would be to create an Unpivot command for each table that has Fee columns in it, and select a sum of the columns needed, depending on the table parameter. That assumes that the tables and their structures are static.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply