Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Scalar function result into temp table column


Scalar function result into temp table column

Author
Message
doug.davidson
doug.davidson
Right there with Babe
Right there with Babe (765 reputation)Right there with Babe (765 reputation)Right there with Babe (765 reputation)Right there with Babe (765 reputation)Right there with Babe (765 reputation)Right there with Babe (765 reputation)Right there with Babe (765 reputation)Right there with Babe (765 reputation)

Group: General Forum Members
Points: 765 Visits: 469
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 Sad
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?
Lynn Pettis
Lynn Pettis
SSC-Insane
SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)

Group: General Forum Members
Points: 24157 Visits: 37926
Can you post the code for your stored procedure and the scalar function?

Cool
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)
doug.davidson
doug.davidson
Right there with Babe
Right there with Babe (765 reputation)Right there with Babe (765 reputation)Right there with Babe (765 reputation)Right there with Babe (765 reputation)Right there with Babe (765 reputation)Right there with Babe (765 reputation)Right there with Babe (765 reputation)Right there with Babe (765 reputation)

Group: General Forum Members
Points: 765 Visits: 469
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
)


Lowell
Lowell
SSChampion
SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)

Group: General Forum Members
Points: 14929 Visits: 38913
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!

doug.davidson
doug.davidson
Right there with Babe
Right there with Babe (765 reputation)Right there with Babe (765 reputation)Right there with Babe (765 reputation)Right there with Babe (765 reputation)Right there with Babe (765 reputation)Right there with Babe (765 reputation)Right there with Babe (765 reputation)Right there with Babe (765 reputation)

Group: General Forum Members
Points: 765 Visits: 469
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.
Lowell
Lowell
SSChampion
SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)

Group: General Forum Members
Points: 14929 Visits: 38913
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!

Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search