• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 751
  • Last Modified:

Using Cursors in a Function

I am trying to create a function that contains a cursor in it.
I get the following errors:

Server: Msg 444, Level 16, State 2, Procedure BusStatus_chk, Line 45
Select statements included within a function cannot return data to a client.

Server: Msg 444, Level 16, State 1, Procedure BusStatus_chk, Line 48
Select statements included within a function cannot return data to a client.

Server: Msg 444, Level 16, State 1, Procedure BusStatus_chk, Line 66
Select statements included within a function cannot return data to a client.

Server: Msg 444, Level 16, State 1, Procedure BusStatus_chk, Line 69
Select statements included within a function cannot return data to a client.

The following is the code for the function:


CREATE FUNCTION DBO.BusStatus_chk( @busreg as varchar(10), @wday as tinyint, @stime as smalldatetime, @endtime as smalldatetime )
RETURNS int
AS
BEGIN

DECLARE @totaltime smallint
DECLARE @cnt int
DECLARE @thisDur smallint

Set @cnt = (Select Count(*) from Bus_Status where Bus_Reg = @busreg AND week_day = @wday)

IF @cnt > 0
BEGIN
     Select @thisDur = DATEDIFF(mi, @stime, @endtime);

     Select @totaltime = SUM(DATEDIFF(mi, Start_time, end_time))
          FROM Bus_Status
          WHERE Bus_Reg=@busreg AND week_day=@wday
END;

IF @totaltime !> 0
BEGIN
     Select @totaltime=0;
END;

IF @thisDur !> 0
BEGIN
     Select @thisDur = 0
END;

Select @totaltime = @totaltime + @thisDur

IF @totaltime > 600
BEGIN
     RETURN 0
END;

DECLARE bustimes_cur CURSOR FOR
     SELECT * FROM Bus_Status
          WHERE Bus_Reg=@busreg AND week_day=@wday
                    AND @stime BETWEEN start_time and end_time

OPEN bustimes_cur
FETCH FIRST FROM bustimes_cur
WHILE @@FETCH_STATUS = 0
BEGIN
        FETCH NEXT FROM bustimes_cur
END
Select @@CURSOR_ROWS

IF @@CURSOR_ROWS > 0
BEGIN
     RETURN 0
END;

CLOSE bustimes_cur
DEALLOCATE bustimes_cur

DECLARE busEndtimes_cur CURSOR FOR
     SELECT * FROM Bus_Status
          WHERE Bus_Reg=@busreg AND week_day=@wday
                    AND end_time BETWEEN @stime and @endtime

OPEN busEndtimes_cur
FETCH FIRST FROM busEndtimes_cur
WHILE @@FETCH_STATUS = 0
BEGIN
        FETCH NEXT FROM busEndtimes_cur
END;

IF @@CURSOR_ROWS > 0
BEGIN
     RETURN 0
END;

CLOSE busEndtimes_cur
DEALLOCATE busEndtimes_cur

RETURN 1
END
--END OF FUNCTION busStatus_chk


PLEASE HELP!!!

thnx!
0
dipti_dalia
Asked:
dipti_dalia
1 Solution
 
adatheladCommented:
You've found one of the limitations of user defined functions. Does it have to be written as a function? You'd find it should be ok if you wrote it as a stored procedure :)
0
 
BulZeyECommented:
You need to get rid of the following:

Select @@CURSOR_ROWS *** this is trying to return a value.

You can select it into a local variable for testing against but just selecting it will try to return it to the client.

HTH
0
 
dipti_daliaAuthor Commented:
Please could you tell me in a bit more detail....I tried saving @@CURSOR_ROWS into a local variable using:
Select @@CURSOR_ROWS INTO @retval, but this still gives the same error.
I also realised that an error is being caused by the use of @@FETCH_STATUS.
Any ideas how I could write this function...I don't see an alternative to the cursors.
0
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
adatheladCommented:
This quote from BOL shows that what you are doing is not valid.

Operations that are allowed inside user defined functions:
Cursor operations referencing local cursors that are declared, opened, closed, and deallocated in the function. Only FETCH statements that assign values to local variables using the INTO clause are allowed; FETCH statements that return data to the client are not allowed.

The fetch statements you have, are returning data to the client which as explained above, is not allowed. These  values returned in FETCH operations must be assigned into variables. You need to use FETCH NEXT FROM...INTO... syntax ash shown in the example below.

e.g.
DECLARE @pstrFieldOne VARCHAR(50)
DECLARE @pstrFieldTwo VARCHAR(50)

DECLARE MyCursor CURSOR FOR
SELECT FieldOne, FieldTwo
FROM MyTable

OPEN MyCursor

FETCH NEXT FROM MyCursor
INTO @pstrFieldOne, @pstrFieldTwo

WHILE @@FETCH_STATUS = 0
BEGIN
   -- Code goes here
   FETCH NEXT FROM MyCursor
   INTO @pstrFieldOne, @pstrFieldTwo
END

CLOSE MyCursor
DEALLOCATE MyCursor


Cheers

0
 
BulZeyECommented:
As for your checking of the cursor rows:

Select @@CURSOR_ROWS

IF @@CURSOR_ROWS > 0
BEGIN
    RETURN 0
END;

You could take out the select statement and check directly against the @@CURSOR_ROWS or put the value into a variable if you wish to access it later:

[Declare your variable ]

DECLARE @NumRowsFromCursor int

[then later in the code...]

SET @NumRowsFromCursor =  @@CURSOR_ROWS

IF @NumRowsFromCursor > 0
BEGIN
    RETURN 0
END

Also, you DEFINATELY need to FETCH the cursor results INTO your local variables for use inside the function.  The example that adathelad  gave is a good one to follow.

HTH

David
0
 
CleanupPingCommented:
dipti_dalia:
This old question needs to be finalized -- accept an answer, split points, or get a refund.  For information on your options, please click here-> http:/help/closing.jsp#1 
EXPERTS:
Post your closing recommendations!  No comment means you don't care.
0
 
monosodiumgCommented:
No comment has been added to this question in more than 268 days, so it is now classified as abandoned.

I will leave the following recommendation for this question in the Cleanup topic area:
   Accept: adathelad http:#8176478

Any objections should be posted here in the next 4 days. After that time, the question will be closed.

monosodiumg
EE Cleanup Volunteer
0

Featured Post

Receive 1:1 tech help

Solve your biggest tech problems alongside global tech experts with 1:1 help.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now