user defined function help

[jmk418]

hi all
im trying to create a stored procedure that will look through a record in a table based on a specific case number. the fields are stored in the table as a 1 or 0 and if they are want i want to store a text value that i can concatenate and return in a varchar variable. here is my part of my code

create function dbo.MorbidityList

	(@Casenum varchar(10))

returns varchar(200)
as
begin

declare @Complications varchar(200)

select
case when O.Surg_Bleed = 1
	then
	case when @Complications = ''
		then @Complications + 'Surgical Bleed'
		else @Complications + ', Surgical Bleed'
	end
	else @Complications
end,
case when O.Surg_Device = 1
	then
	case when @Complications = ''
		then @Complications + 'Surgical Device'
		else @Complications + ', Surgical Device'
	end
	else @Complications
end,
case when O.Other_Surg is not null
	then
	case when @Complications = ''
		then @Complications + O.Other_Surg
		else @Complications + ', ' + O.Other_Surg
	end
	else @Complications
end

from
Outcomes O

where
@Casenum = O.Casenum

return @Complications

end

this checks fine when i do a syntax check but when i try to run it i get the following error

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

does anyone have any ideas why im getting this error or a better way i can accomplish this.
any help is appreciated
thanks
jeremy

 

[bborissov]

Create a Stored procedure with output parameter instead of UDF.

Borislav Borissov

 

[jmk418]

im not sure what you mean by stored procedure with output parameter. i want to call this function in a stored procedure that is going to pull other data from the case that is stored in different tables. basically i just want a list of complications that i can put in a report in a readable format hence adding the commas.
thanks
jeremy