EXEC in UDF

  • 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!

  • 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

  • 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