SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


EXEC in UDF


EXEC in UDF

Author
Message
levsha_z
levsha_z
SSC-Enthusiastic
SSC-Enthusiastic (184 reputation)SSC-Enthusiastic (184 reputation)SSC-Enthusiastic (184 reputation)SSC-Enthusiastic (184 reputation)SSC-Enthusiastic (184 reputation)SSC-Enthusiastic (184 reputation)SSC-Enthusiastic (184 reputation)SSC-Enthusiastic (184 reputation)

Group: General Forum Members
Points: 184 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!
noeld
noeld
SSC-Insane
SSC-Insane (23K reputation)SSC-Insane (23K reputation)SSC-Insane (23K reputation)SSC-Insane (23K reputation)SSC-Insane (23K reputation)SSC-Insane (23K reputation)SSC-Insane (23K reputation)SSC-Insane (23K reputation)

Group: General Forum Members
Points: 23788 Visits: 2048
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
Glen Sidelnikov
Glen Sidelnikov
SSCommitted
SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)

Group: General Forum Members
Points: 1903 Visits: 804
http://jimbojw.com/wiki/index.php?title=SQL_2005_-_Why_You_Can't_EXEC()_Inside_a_UDF
GSquared
GSquared
SSC Guru
SSC Guru (61K reputation)SSC Guru (61K reputation)SSC Guru (61K reputation)SSC Guru (61K reputation)SSC Guru (61K reputation)SSC Guru (61K reputation)SSC Guru (61K reputation)SSC Guru (61K reputation)

Group: General Forum Members
Points: 61041 Visits: 9730
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
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search