Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Scalar function result into temp table column Expand / Collapse
Author
Message
Posted Monday, April 8, 2013 12:55 PM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Yesterday @ 2:03 PM
Points: 272, Visits: 140
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?

Post #1439987
Posted Monday, April 8, 2013 1:22 PM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Yesterday @ 6:47 PM
Points: 23,396, Visits: 32,229
Can you post the code for your stored procedure and the scalar function?



Lynn Pettis

For better assistance in answering your questions, click here
For tips to get better help with Performance Problems, click here
For Running Totals and its variations, click here or when working with partitioned tables
For more about Tally Tables, click here
For more about Cross Tabs and Pivots, click here and here
Managing Transaction Logs

SQL Musings from the Desert Fountain Valley SQL (My Mirror Blog)
Post #1439998
Posted Monday, April 8, 2013 1:28 PM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Yesterday @ 2:03 PM
Points: 272, Visits: 140
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
)

Post #1440000
Posted Monday, April 8, 2013 1:37 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 5:22 AM
Points: 12,916, Visits: 32,078
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

--There is no spoon, and there's no default ORDER BY in sql server either.
Actually, Common Sense is so rare, it should be considered a Superpower. --my son
Post #1440003
Posted Monday, April 8, 2013 1:48 PM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Yesterday @ 2:03 PM
Points: 272, Visits: 140
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.
Post #1440006
Posted Monday, April 8, 2013 1:51 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 5:22 AM
Points: 12,916, Visits: 32,078
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

--There is no spoon, and there's no default ORDER BY in sql server either.
Actually, Common Sense is so rare, it should be considered a Superpower. --my son
Post #1440009
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse