April 8, 2013 at 12:55 pm
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?
April 8, 2013 at 1:22 pm
Can you post the code for your stored procedure and the scalar function?
April 8, 2013 at 1:28 pm
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
)
April 8, 2013 at 1:37 pm
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
April 8, 2013 at 1:48 pm
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.
April 8, 2013 at 1:51 pm
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
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply