EXEC in UDF - SQL Server Forums

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)

EXEC in UDF

Author

Topic

levsha
Starting Member

4 Posts

Posted - 2008-09-04 : 10:19:53

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!

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-09-04 : 10:22:21

you cant use INSERT EXEC inside UDF as suggested. make it a procedure and call it wherever you want.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-09-04 : 10:35:01

What you need is a view for the table where you normalize the data.

CREATE VIEW dbo.vwMyTable1
AS

SELECT u.FeeID, u.theValue, u.theCol
FROM MyTable1 AS t
UNPIVOT ( theValue FOR theCol IN (t.fee1, t.fee2, t.fee3)
) AS u

E 12°55'05.63"
N 56°04'39.26"

levsha
Starting Member

4 Posts

Posted - 2008-09-04 : 10:54:45

quote:
Originally posted by visakh16

you cant use INSERT EXEC inside UDF as suggested. make it a procedure and call it wherever you want.

How could I call a procedure from a select statement?
I need to use an output on a regular select list.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-09-04 : 10:58:33

quote:
Originally posted by levsha

quote:
Originally posted by visakh16

you cant use INSERT EXEC inside UDF as suggested. make it a procedure and call it wherever you want.

How could I call a procedure from a select statement?
I need to use an output on a regular select list.

you can call it using OPENROWSET

http://sqlblogcasts.com/blogs/madhivanan/archive/2007/11/26/select-columns-from-exec-procedure-name-is-this-possible.aspx

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-09-04 : 11:01:23

Select *
from OPENROWSET('SQLOLEDB','Data Source=MyServer;Trusted_Connection=yes;Integrated Security=SSPI', 'Execute DB1..MyProc1')

E 12°55'05.63"
N 56°04'39.26"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources