Scalar function result into temp table column

  • Recently I found that I could not have the return of scalar function as part of an update statement on a temporary table within a stored procedure.

    I would receive the error:

    Only functions and some extended stored procedures can be executed from within a function.

    My statement before finding this out.

    update rmc

    set rmc.sla_target_date = dbo.fn_GetTargetTimeInServiceHours(rmc.project_id, rmc.folder_id, rmc.sla_start_date, (rmc.hold_time + @target_time))

    from #request_meet_criteria rmc

    where rmc.smx_active_flag = 0

    Upon learning this behaviour, I have worked around it with a cursor 🙁

    Set for each row, dump the value into a variable, the update the table row with variable.

    declare csr_spr_ibm_generic_sla_attainment cursor for

    select project_id, request_id, folder_id, sla_start_date, hold_time, target_time

    from #request_meet_criteria

    where smx_active_flag = 0 and hold_time > 0

    open csr_spr_ibm_generic_sla_attainment

    fetch next from csr_spr_ibm_generic_sla_attainment

    into @c_project_id, @c_request_id, @c_folder_id, @c_open_date, @c_hold_time, @c_target_time

    while (@@FETCH_STATUS = 0)

    begin

    select @sla_target_date = dbo.fn_GetTargetTimeInServiceHours(@c_project_id, @c_folder_id, @c_open_date, (@c_hold_time + @c_target_time))

    update rmc

    set rmc.sla_target_date = isnull(@sla_target_date, rmc.sla_target_date)

    from #request_meet_criteria rmc

    where rmc.project_id = @c_project_id and rmc.request_id = @c_request_id

    fetch next from csr_spr_ibm_generic_sla_attainment

    into @c_project_id, @c_request_id, @c_folder_id, @c_open_date, @c_hold_time, @c_target_time

    end-- while loop for csr_outage_list_spr_ibm_generic_sla_availability

    close csr_spr_ibm_generic_sla_attainment

    deallocate csr_spr_ibm_generic_sla_attainment

    Is there a more optimal solution?

    Can I use the function within the update statement in some other syntactical manner?

  • Can you post the code for your stored procedure and the scalar function?

  • Those snippets are directly extracted from the stored procedure in its before and after state.

    Here is the function

    if exists (select * from sysobjects where name='fn_GetTargetTimeInServiceHours' and type='FN')

    drop function fn_GetTargetTimeInServiceHours

    go

    /*----------------------------------------------------------------------------------------------------------------

    fn_GetTargetTimeInServiceHours - used to add/subtract a number of months from a integer year and month, this can return

    either the year or the month

    - pass in: project_id, folder_id, start date/time (GMT), time (in seconds)

    - returns: the actual target date

    @project_id - integer value that represents the project in question

    @folder_id - integer value that represents the folder service hours to use

    @start_date_time - start date time to calculate @time_in_secs from

    @time_in_secs - Time in seconds to determine the target date/time in reference to the start_date_time

    based on the folder service hours. Note, this time should include any calculated hold

    time that you want to factor into this.

    ----------------------------------------------------------------------------------------------------------------*/

    CREATE FUNCTION fn_GetTargetTimeInServiceHours(@project_id int, @folder_id int, @start_date_range datetime, @time_in_secs int)

    RETURNS datetime

    AS

    BEGIN

    declare @target_date_time datetime, @GMT_now datetime, @service_fragment int

    if @start_date_range is null

    begin

    exec spg_get_gmt_now @GMT_now = @GMT_now output

    return (@GMT_now)

    end

    else

    if (@project_id is null or @folder_id is null or @start_date_range is null or @time_in_secs is null)

    return (@start_date_range)

    /*---------------------------------------------------------------------------------------------

    If no service hours simply return the start date time + the time_in_secs.

    ---------------------------------------------------------------------------------------------*/

    if not exists (select 1 from service_hour sh (nolock) where sh.project_id = @project_id and sh.folder_id = @folder_id

    and sh.start_time >= @start_date_range and sh.start_time <= dateadd(mm, 1, @start_date_range))

    begin

    select @target_date_time = DATEADD(s, @time_in_secs, @start_date_range)

    end

    if not @target_date_time is null

    return (@target_date_time)

    else

    begin

    select @target_date_time = @start_date_range

    while @time_in_secs > 0

    begin

    select @service_fragment = 0

    if exists (select 1 from service_hour sh (nolock)

    where sh.project_id = @project_id

    and sh.folder_id = @folder_id

    and @target_date_time >= sh.start_time

    and @target_date_time <= DATEADD(s, sh.service_seconds, sh.start_time)

    and sh.service_seconds > 0)

    begin

    select top 1 @service_fragment = datediff(ss, @target_date_time, DATEADD(s, sh.service_seconds, sh.start_time))

    from service_hour sh (nolock)

    where sh.project_id = @project_id

    and sh.folder_id = @folder_id

    and @target_date_time >= sh.start_time

    and @target_date_time <= DATEADD(s, sh.service_seconds, sh.start_time)

    and sh.service_seconds > 0

    if @time_in_secs > @service_fragment

    select @target_date_time = dateadd(ss, @service_fragment + 1, @target_date_time),

    @time_in_secs = @time_in_secs - @service_fragment

    else

    if @time_in_secs < @service_fragment

    select @target_date_time = dateadd(ss, @time_in_secs, @target_date_time),

    @time_in_secs = 0

    else

    select @target_date_time = dateadd(ss, @service_fragment, @target_date_time),

    @time_in_secs = 0

    end

    else

    begin

    /*---------------------------------------------------------------------------------------------

    Check to see if we have run out of service hours

    ---------------------------------------------------------------------------------------------*/

    if exists (select 1 from service_hour sh (nolock)

    where sh.project_id = @project_id

    and sh.folder_id = @folder_id

    and sh.start_time > @target_date_time

    and sh.start_time <= DATEADD(mm, 1, @target_date_time)

    and sh.service_seconds > 0)

    /*---------------------------------------------------------------------------------------------

    Move target_date_time to beginning of next available service day

    ---------------------------------------------------------------------------------------------*/

    select @target_date_time = min(sh.start_time)

    from service_hour sh (nolock)

    where sh.project_id = @project_id

    and sh.folder_id = @folder_id

    and sh.start_time > @target_date_time

    and sh.start_time <= DATEADD(mm, 1, @target_date_time)

    and sh.service_seconds > 0

    else

    begin

    /*---------------------------------------------------------------------------------------------

    Service Hours have run out, add remaining time assuming 24x7 service hours

    ---------------------------------------------------------------------------------------------*/

    select @target_date_time = dateadd(ss, @time_in_secs, @target_date_time)

    select @time_in_secs = 0

    end

    end

    end

    end

    return(@target_date_time)

    END

    go

    grant execute on fn_GetTargetTimeInServiceHours to public

    go

    Here is the temp table definition

    create table #request_meet_criteria (

    project_id int not null,

    request_id int not null,

    folder_id int,

    category_code char(5) collate database_default,

    subcategory_code char(5) collate database_default,

    module_id int,

    resolution_id int,

    opened_date datetime,

    include_task_id int not null,

    include_date datetime not null,

    sla_start_date datetime,

    sla_target_date datetime,

    sla_complete_date datetime,

    consumed_time int not null,

    hold_time int not null,

    sla_consumed_time int not null,

    target_time int not null,

    smx_active_flag int,

    met_sla char(1) collate database_default not null

    )

  • without re-writing the scalar function to be an inline table valued function, i though getting intermediate results for the scalar function in a CTE or sub select would be easier:

    i'm assuming the first two not null columns constitute a PK for the data at hand:

    WITH myIntermediateResults

    AS

    (

    SELECT rmc.*,

    NewTargetDate = dbo.fn_GetTargetTimeInServiceHours(rmc.project_id, rmc.folder_id, rmc.sla_start_date, (rmc.hold_time + @target_time))

    FROM #request_meet_criteria rmc

    rmc.smx_active_flag = 0

    )

    update rmc

    set rmc.sla_target_date = myIntermediateResults.NewTargetDate

    from #request_meet_criteria rmc

    INNER JOIN myIntermediateResults

    ON rmc.project_id = myIntermediateResults.project_id

    AND rmc.request_id = myIntermediateResults.request_id

    Lowell


    --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

  • I have never explored a CTE before. Good new research and learning for me.

    I will use the sub select as the new work around until I finish my learning on the CTE approach.

    Thanks for you time everyone.

  • doug.davidson (4/8/2013)


    I have never explored a CTE before. Good new research and learning for me.

    I will use the sub select as the new work around until I finish my learning on the CTE approach.

    Thanks for you time everyone.

    just noticed the forum was SQL2000, so here's the same thing as a sub select, if it wasn't obvious how to convert the first example:

    update rmc

    set rmc.sla_target_date = dbo.fn_GetTargetTimeInServiceHours(rmc.project_id, rmc.folder_id, rmc.sla_start_date, (rmc.hold_time + @target_time))

    from #request_meet_criteria rmc

    INNER JOIN (

    SELECT rmc.*,

    NewTargetDate = dbo.fn_GetTargetTimeInServiceHours(rmc.project_id, rmc.folder_id, rmc.sla_start_date, (rmc.hold_time + @target_time))

    FROM #request_meet_criteria rmc

    rmc.smx_active_flag = 0

    ) myIntermediateResults

    ON rmc.project_id = myIntermediateResults.project_id

    AND rmc.request_id = myIntermediateResults.request_id

    Lowell


    --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply