Select statements included within a function cannot return data to a client

Question

T-SQL gurus, what is causing the following procedure to return this error "Select statements included within a function cannot return data to a client."?

create function dbo.func_get_times_for_stop(@stopId as varchar(5))
returns varchar(max) as
begin 
    declare @arrival_times varchar(max)
    set @arrival_times = '';
    declare @arrival_time varchar(5)
    declare arrival_cursor cursor for
        select arrival from schedules where stopid=@stopId;

    open arrival_cursor;
    fetch next from arrival_cursor;
    while @@fetch_status = 0
       begin
          fetch next from arrival_cursor into @arrival_time;    
          set @arrival_times += ',' + @arrival_time;    
       end;
    close arrival_cursor;
    deallocate arrival_cursor;

    return  @arrival_times;
end;

I am simply trying to return a scalar which comprises the string concatenated from arrival times. I do not see where I am returning a select statement.

Related: [SQL Server: Can I comma delimit...](http://stackoverflow.com/questions/2046037/sql-server-can-i-comma-delimit-multiple-rows-into-one-column) — OMG Ponies, Feb 26 '12 at 05:33
@OMG Ponies, thanks for related link. This would be even nice if I can get it to work in my case. — Klaus Nji, Feb 26 '12 at 13:01

score 3 · Accepted Answer · answered Feb 26 '12 at 05:34

3

Could it be this line?

fetch next from arrival_cursor;

It should be fetching into @arrival_time.

answered Feb 26 '12 at 05:34

John Pick

5,457
29
31

score 1 · Answer 2 · answered Feb 26 '12 at 05:37

1

Here:

fetch next from arrival_cursor;

you need

fetch next from arrival_cursor into @arrival_time;

answered Feb 26 '12 at 05:37

Oleg Dok

20,590
4
43
54

Select statements included within a function cannot return data to a client

2 Answers2