UDF output returns 1/2 - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

UDF output returns 1/2

Author

Topic

MPEvans
Starting Member

7 Posts

Posted - 2006-03-03 : 09:55:38

I've got a scalar UDF that is meant to return varchar(8000) but actually only returns 4000 characters. I'm guessing this is a nvarchar/varchar problem but I can't see why/where. The UDF is as follows:

CREATE  function dbo.udf_getViewFields(@headerId int) returns varchar(8000)
as
begin
	declare @csv varchar(8000)
	select @csv = Isnull(@csv, '') + ', max(case when fieldID=' + cast(dbo.clFields.fieldID as varchar(5)) + ' then fieldvalue else null end) as [' + dbo.clFields.fieldName + ']'
	from  dbo.iMain INNER JOIN
	                      dbo.clFields ON dbo.iMain.fieldID = dbo.clFields.FieldID INNER JOIN
	                      dbo.bTypes ON dbo.clFields.bTypeId = dbo.bTypes.bTypeId
	WHERE (dbo.iMain.headerID = @headerId)
	Return @csv
end

What could be causing this?
Thanks,
Martin

MPEvans
Starting Member

7 Posts

Posted - 2006-03-06 : 04:20:00

Some more info...

I tested the UDF output by hard-coding the return value to a 8000 character string which works as you would expect, so for some reason it is the SELECT @ that is limited to 4000 characters, but I don't understand why. Are there any settings at DB level that might do this?

And, pushing my luck perhaps, any suggestions on how to go about making the SELECT @ cater for the potential scenario of more than 8000 (or 4000) characters?

Thanks,
Martin

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2006-03-06 : 07:07:24

Search for 8000 in this topic
http://sqlteam.com/forums/topic.asp?TOPIC_ID=55210

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources