Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

EXEC in UDF Expand / Collapse
Author
Message
Posted Thursday, September 4, 2008 8:14 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Thursday, September 4, 2008 9:52 AM
Points: 28, Visits: 68
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!
Post #563841
Posted Thursday, September 4, 2008 8:59 AM
SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Tuesday, September 16, 2014 5:47 AM
Points: 6,259, Visits: 2,029
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
Post #563885
Posted Thursday, September 4, 2008 9:17 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Monday, August 5, 2013 12:19 PM
Points: 371, Visits: 797
http://jimbojw.com/wiki/index.php?title=SQL_2005_-_Why_You_Can't_EXEC()_Inside_a_UDF
Post #563909
Posted Thursday, September 4, 2008 9:44 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Friday, June 27, 2014 12:43 PM
Points: 13,872, Visits: 9,596
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
Post #563933
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse