?
Solved

Using Cursors in a Function

Posted on 2003-03-20
8
Medium Priority
?
745 Views
Last Modified: 2012-08-13
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
Comment
Question by:dipti_dalia
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
8 Comments
 
LVL 23

Expert Comment

by:adathelad
ID: 8174497
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
 
LVL 4

Expert Comment

by:BulZeyE
ID: 8174504
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
 

Author Comment

by:dipti_dalia
ID: 8176231
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
Learn how to optimize MySQL for your business need

With the increasing importance of apps & networks in both business & personal interconnections, perfor. has become one of the key metrics of successful communication. This ebook is a hands-on business-case-driven guide to understanding MySQL query parameter tuning & database perf

 
LVL 23

Accepted Solution

by:
adathelad earned 400 total points
ID: 8176478
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
 
LVL 4

Expert Comment

by:BulZeyE
ID: 8178645
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
 

Expert Comment

by:CleanupPing
ID: 9275689
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
 
LVL 12

Expert Comment

by:monosodiumg
ID: 11183559
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

Get MySQL database support online, now!

At Percona’s web store you can order your MySQL database support needs in minutes. No hassles, no fuss, just pick and click. Pay online with a credit card.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Slowly Changing Dimension Transformation component in data task flow is very useful for us to manage and control how data changes in SSIS.
This article shows gives you an overview on SQL Server 2016 row level security. You will also get to know the usages of row-level-security and how it works
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.
Suggested Courses

770 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question