Question about functions

22 Sep 2005 2:54 PM

Amy

Hi,

I need to call a function in a sql query in a stored procedure to
calculate time differences between various dates. I have a function
that uses a cursor to sum up the totals of these numbers, but it runs
very, very slowly. I can accomplish the same results without a cursor
by using a temporary table and several queries, but when I try to put
this in a stored procedure and call the stored procedure from the
function, I get the following error:
Only functions and extended stored procedures can be executed from
within a function.

Any suggestions?

Thanks,
Amy Bolden

22 Sep 2005 2:59 PM

Anith Sen

>> Any suggestions?

The message clearly states what you can do with a function. So you will have
to find an alternative to calling procedures from functions. Either make the
calling routine a procedure or make the called routine a function.

--
Anith

22 Sep 2005 3:04 PM

Jens

You must supply more information about that, did you try to use
datediff in a correlated query (don´t know where you get the data from
?).

Jens Suessmeyer.

22 Sep 2005 3:30 PM

Amy Bolden

Sorry, I thought maybe an explanation would be enough.

Here is the top query in the stored procedure. The @StartDate and
@EndDate are
parameters that are supplied by a web application:

SELECT DISTINCT(CONVERT(VARCHAR(10), TT.DateTS, 101)) AS ActivityDate,
    POSUM.UserName,
    TT.UserId,
    dbo.fnTimeInTruckInMinutes(TT.UserId,TT.DateTS) AS TimeInTruck
    FROM vw_TTLH TT INNER JOIN
    UserMaster POSUM ON TT.UserID = POSUM.UserNum
    WHERE DateTS BETWEEN @StartDate AND @EndDate)
    GROUP BY POSUM.UserName, TT.UserID, POSUM.UserNum,
    CONVERT(VARCHAR(10), TT.DateTS, 101), TT.DateTS

Here is the function that calculates all the time spent in a truck for a
particular day:

CREATE FUNCTION dbo.fnTimeInTruckInMinutes (@UserID int = NULL,
@ActivityDate DateTime = NULL)
RETURNS int
AS
BEGIN
    DECLARE @TotalTimeInTruck    INT

    Exec spGetTotalTimeInTruck @UserID, @ActivityDate, @TotalTimeInTruck
    RETURN @TotalTimeInTruck
END

Here is the stored procedure that I am trying to call from the function
to get the total time
spent in a truck on a single day:

CREATE PROCEDURE spGetTotalTimeInTruck
    @UserID        INT,
    @ActivityDate        VARCHAR(10),
    @TotalTimeInTruck    INT     OUTPUT
AS
DECLARE @ActivityDatePlusOne    VARCHAR(10)
SET @ActivityDatePlusOne = DATEADD(d, 1, @ActivityDate)

CREATE TABLE #tempTruck
(
SeqNum int NULL,
InTruck datetime NULL,
OutTruck datetime NULL
)

INSERT INTO #tempTruck
(OrderNumber, InTruck)
SELECT OrderNumber, DateTS
FROM vw_TTLH
WHERE USerID = @UserID
AND DateTimeStamp >= @ActivityDate
AND DateTimeStamp <= @ActivityDatePlusOne
AND ActID in (200)

UPDATE #tempTruck
SET OutTruck = (
SELECT DateTS
FROM vw_TTLH
WHERE USerID = @UserID
AND DateTimeStamp >= @ActivityDate
AND DateTimeStamp <= @ActivityDatePlusOne
AND OrderNumber = #tempTruck.OrderNumber + 1)

SELECT SUM(DateDiff(n, InTruck, OutTruck)) FROM #tempTruck

DROP TABLE #tempTruck
RETURN @TotalTimeInTruck

Thanks,
Amy Bolden

*** Sent via Developersdex http://www.developersdex.com ***

22 Sep 2005 4:02 PM

Payson

You might try eliminating the temp table and using a derived table in
its place. Something like this: (COMPLETELY UNTESTED)

CREATE PROCEDURE spGetTotalTimeInTruck @UserID INT, @ActivityDate
VARCHAR(10), @TotalTimeInTruck INT OUTPUT
AS

SELECT -- Should "@TotalTimeInTruck = " go here??
   SUM(DateDiff(n, InTruck, OutTruck)) AS
FROM (    SELECT OrderNumber, DateTS AS InTruck,
    (    SELECT DateTS
         FROM vw_TTLH ttlh2
         WHERE USerID = @UserID
           AND DateTimeStamp >= @ActivityDate
           AND DateTimeStamp <= DATEADD(d, 1, @ActivityDate)
           AND OrderNumber = ttlh1.OrderNumber + 1) AS OutTruck
    FROM vw_TTLH ttlh1
    WHERE USerID = @UserID
    AND DateTimeStamp >= @ActivityDate
    AND DateTimeStamp <= DATEADD(d, 1, @ActivityDate)
    AND ActID in (200)
      ) ttlh_d

RETURN @TotalTimeInTruck

You might then consider making the function an inline function (put the
select inline - "return select ..."). The optimizer seems to like that
better than procedural code.

Good luck.

Payson

Amy Bolden wrote:
Show quote

> Sorry, I thought maybe an explanation would be enough.
>
> Here is the top query in the stored procedure. The @StartDate and
> @EndDate are
> parameters that are supplied by a web application:
>
> SELECT DISTINCT(CONVERT(VARCHAR(10), TT.DateTS, 101)) AS ActivityDate,
>     POSUM.UserName,
>     TT.UserId,
>     dbo.fnTimeInTruckInMinutes(TT.UserId,TT.DateTS) AS TimeInTruck
>     FROM vw_TTLH TT INNER JOIN
>     UserMaster POSUM ON TT.UserID = POSUM.UserNum
>     WHERE DateTS BETWEEN @StartDate AND @EndDate)
>     GROUP BY POSUM.UserName, TT.UserID, POSUM.UserNum,
>     CONVERT(VARCHAR(10), TT.DateTS, 101), TT.DateTS
>
> Here is the function that calculates all the time spent in a truck for a
> particular day:
>
> CREATE FUNCTION dbo.fnTimeInTruckInMinutes (@UserID int = NULL,
> @ActivityDate DateTime = NULL)
> RETURNS int
> AS
> BEGIN
>     DECLARE @TotalTimeInTruck    INT
>
>
>     Exec spGetTotalTimeInTruck @UserID, @ActivityDate, @TotalTimeInTruck
>     RETURN @TotalTimeInTruck
> END
>
> Here is the stored procedure that I am trying to call from the function
> to get the total time
> spent in a truck on a single day:
>
> CREATE PROCEDURE spGetTotalTimeInTruck
>     @UserID        INT,
>     @ActivityDate        VARCHAR(10),
>     @TotalTimeInTruck    INT     OUTPUT
> AS
> DECLARE @ActivityDatePlusOne    VARCHAR(10)
> SET @ActivityDatePlusOne = DATEADD(d, 1, @ActivityDate)
>
> CREATE TABLE #tempTruck
> (
> SeqNum int NULL,
> InTruck datetime NULL,
> OutTruck datetime NULL
> )
>
> INSERT INTO #tempTruck
> (OrderNumber, InTruck)
> SELECT OrderNumber, DateTS
> FROM vw_TTLH
> WHERE USerID = @UserID
> AND DateTimeStamp >= @ActivityDate
> AND DateTimeStamp <= @ActivityDatePlusOne
> AND ActID in (200)
>
> UPDATE #tempTruck
> SET OutTruck = (
> SELECT DateTS
> FROM vw_TTLH
> WHERE USerID = @UserID
> AND DateTimeStamp >= @ActivityDate
> AND DateTimeStamp <= @ActivityDatePlusOne
> AND OrderNumber = #tempTruck.OrderNumber + 1)
>
> SELECT SUM(DateDiff(n, InTruck, OutTruck)) FROM #tempTruck
>
> DROP TABLE #tempTruck
> RETURN @TotalTimeInTruck
>
> Thanks,
> Amy Bolden
>
> *** Sent via Developersdex http://www.developersdex.com ***

22 Sep 2005 4:03 PM

Payson

You might try eliminating the temp table and using a derived table in
its place. Something like this: (COMPLETELY UNTESTED)

CREATE PROCEDURE spGetTotalTimeInTruck @UserID INT, @ActivityDate
VARCHAR(10), @TotalTimeInTruck INT OUTPUT
AS

SELECT -- Should "@TotalTimeInTruck = " go here??
   SUM(DateDiff(n, InTruck, OutTruck)) AS
FROM (    SELECT OrderNumber, DateTS AS InTruck,
    (    SELECT DateTS
         FROM vw_TTLH ttlh2
         WHERE USerID = @UserID
           AND DateTimeStamp >= @ActivityDate
           AND DateTimeStamp <= DATEADD(d, 1, @ActivityDate)
           AND OrderNumber = ttlh1.OrderNumber + 1) AS OutTruck
    FROM vw_TTLH ttlh1
    WHERE USerID = @UserID
    AND DateTimeStamp >= @ActivityDate
    AND DateTimeStamp <= DATEADD(d, 1, @ActivityDate)
    AND ActID in (200)
      ) ttlh_d

RETURN @TotalTimeInTruck

You might then consider making the function an inline function (put the
select inline - "return select ..."). The optimizer seems to like that
better than procedural code.

Good luck.

Payson

Amy Bolden wrote:
Show quote

> Sorry, I thought maybe an explanation would be enough.
>
> Here is the top query in the stored procedure. The @StartDate and
> @EndDate are
> parameters that are supplied by a web application:
>
> SELECT DISTINCT(CONVERT(VARCHAR(10), TT.DateTS, 101)) AS ActivityDate,
>     POSUM.UserName,
>     TT.UserId,
>     dbo.fnTimeInTruckInMinutes(TT.UserId,TT.DateTS) AS TimeInTruck
>     FROM vw_TTLH TT INNER JOIN
>     UserMaster POSUM ON TT.UserID = POSUM.UserNum
>     WHERE DateTS BETWEEN @StartDate AND @EndDate)
>     GROUP BY POSUM.UserName, TT.UserID, POSUM.UserNum,
>     CONVERT(VARCHAR(10), TT.DateTS, 101), TT.DateTS
>
> Here is the function that calculates all the time spent in a truck for a
> particular day:
>
> CREATE FUNCTION dbo.fnTimeInTruckInMinutes (@UserID int = NULL,
> @ActivityDate DateTime = NULL)
> RETURNS int
> AS
> BEGIN
>     DECLARE @TotalTimeInTruck    INT
>
>
>     Exec spGetTotalTimeInTruck @UserID, @ActivityDate, @TotalTimeInTruck
>     RETURN @TotalTimeInTruck
> END
>
> Here is the stored procedure that I am trying to call from the function
> to get the total time
> spent in a truck on a single day:
>
> CREATE PROCEDURE spGetTotalTimeInTruck
>     @UserID        INT,
>     @ActivityDate        VARCHAR(10),
>     @TotalTimeInTruck    INT     OUTPUT
> AS
> DECLARE @ActivityDatePlusOne    VARCHAR(10)
> SET @ActivityDatePlusOne = DATEADD(d, 1, @ActivityDate)
>
> CREATE TABLE #tempTruck
> (
> SeqNum int NULL,
> InTruck datetime NULL,
> OutTruck datetime NULL
> )
>
> INSERT INTO #tempTruck
> (OrderNumber, InTruck)
> SELECT OrderNumber, DateTS
> FROM vw_TTLH
> WHERE USerID = @UserID
> AND DateTimeStamp >= @ActivityDate
> AND DateTimeStamp <= @ActivityDatePlusOne
> AND ActID in (200)
>
> UPDATE #tempTruck
> SET OutTruck = (
> SELECT DateTS
> FROM vw_TTLH
> WHERE USerID = @UserID
> AND DateTimeStamp >= @ActivityDate
> AND DateTimeStamp <= @ActivityDatePlusOne
> AND OrderNumber = #tempTruck.OrderNumber + 1)
>
> SELECT SUM(DateDiff(n, InTruck, OutTruck)) FROM #tempTruck
>
> DROP TABLE #tempTruck
> RETURN @TotalTimeInTruck
>
> Thanks,
> Amy Bolden
>
> *** Sent via Developersdex http://www.developersdex.com ***

22 Sep 2005 3:35 PM

Robbe Morris [C# MVP]

Are you talking about something similar to this?

http://www.eggheadcafe.com/articles/20030626.asp

--
Robbe Morris - 2004/2005 Microsoft MVP C#
Free Source Code for ADO.NET Object Mapper To DataBase Tables And Stored
Procedures
http://www.eggheadcafe.com/articles/adonet_source_code_generator.asp

Show quote

"Amy" <abol***@eastridge.net> wrote in message
news:1127400856.741551.164370@z14g2000cwz.googlegroups.com...
> Hi,
>
> I need to call a function in a sql query in a stored procedure to
> calculate time differences between various dates. I have a function
> that uses a cursor to sum up the totals of these numbers, but it runs
> very, very slowly. I can accomplish the same results without a cursor
> by using a temporary table and several queries, but when I try to put
> this in a stored procedure and call the stored procedure from the
> function, I get the following error:
> Only functions and extended stored procedures can be executed from
> within a function.
>
> Any suggestions?
>
> Thanks,
> Amy Bolden
>

22 Sep 2005 4:04 PM

Akbar khan is a Senior Database develope

Hi

I think if you omitt using of CURSUR in your Function then it will be some
more faster. If you are using cursor for itration purpose than I have an Idea
that may help you.
There is no concept of Arrays in SQL Server I think so, but we can create
our own Psedu Arrays, and we can itrate in these arrays.
Define a local variable as varchar and insert the Primary key in the varable
COMMA seprated.
And then by a while loop get the ID and select the field(s) you want from
the orignal table and colculate it.

Run This code It may open your Mind

Declare @var varchar(100)
SET @var = '120,20,23,32,23234,,3,5,6,'
WHILE @var <> ''
BEGIN
    Declare @id int
    SET @id = CAST(SUBSTRING(@var,0, CharIndex(',', @var,0)) as int)
    SET @var = SUBSTRING(@var, CharIndex(',', @var,0)+1, LEN(@var))
    PRINT @id
END
__________________________________________________________

Show quote

"Amy" wrote:

> Hi,
>
> I need to call a function in a sql query in a stored procedure to
> calculate time differences between various dates. I have a function
> that uses a cursor to sum up the totals of these numbers, but it runs
> very, very slowly. I can accomplish the same results without a cursor
> by using a temporary table and several queries, but when I try to put
> this in a stored procedure and call the stored procedure from the
> function, I get the following error:
> Only functions and extended stored procedures can be executed from
> within a function.
>
> Any suggestions?
>
> Thanks,
> Amy Bolden
>
>