returning varchar(MAX) from a function returns truncated string

Question

I am having a strange problem. I have a function which returns a large string by concatenating several other strings.

In some cases the string is too long and is getting truncated.

For example, there is an instance when a string of length 379999 is supposed to be returned, but what I see is that the string is truncated and the length is only 65536.

I am sure that varchar(MAX) can hold a string greater that 65536, but where am I going wrong here? The function has been shown below.

[UPDATE]

this function is being used in several stored procedures, and the stored procedures are used by the crystal reports to display data.

[UPDATE END]

ALTER FUNCTION [dbo].[GetShipContSernText](
@shipContNum numeric(9)) returns Varchar(MAX) begin

declare serns cursor for


select
    serial_number
from
    serial_number_view
where
    ship_cont_num = @shipContNum
    and
    template_id is null

open serns;

declare @text varchar(MAX);
declare @serialNumber nvarchar(50);

fetch next from serns into @serialNumber;

while (@@FETCH_STATUS = 0)
begin
    -- cannot concat a null string.

    if (@text is null)
             set @text = @serialNumber; 
    else
          set @text = @text + N', ' + @serialNumber;
    end 
    fetch next from serns into @serialNumber;
end;

close serns;
deallocate serns;

return @text;
end

Answer 1

Is there a reason you can't return the rows and concatenate in the code?

You are mixing varchar and nvarchar.

Also you need to use SQL Native Client to be able to use varchar(max) as a return value.

Answer 2

the function is indeed returning a sting more than 65536 characters, i used the LEN function and found the length to be much greater. It was the grid which was restricting the length. Now i need to find why is the crystal report truncating the string. Bunch of thanks to Jonas :)

Answer 3

There is a bug in Crystal Reports which makes it interpret varchar(max) fields as varchar(255)

see here: http://www.crystalreportsbook.com/Forum/forum_posts.asp?TID=5843&PID=17503