Question about functions

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>> 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|||You must supply more information about that, did you try to use
datediff in a correlated query (don=B4t know where you get the data from
?).
Jens Suessmeyer.|||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.examnotes.net ***|||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...e_generator.asp
"Amy" <abolden@.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
>|||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:
> 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.examnotes.net ***|||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:
> 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.examnotes.net ***|||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 Ide
a
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
________________________________________
__________________
"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
>