concatenate variable in a a function 1

[jmk418]

hi all
i have a table that contains patient complications. My ultimate goal is to create a report using reporting services that will display the complications. I want to create a list of complications seperated by ", ". I was trying to accomplish this using a function that would run through a patients record and if they have a complication store it in a variable and any subsequent complications would be added to the variable with a ", " so that the result would look like "compliation1, complications2..."
Here is what i have now.

create function dbo.MorbidityList

	(@Casenum varchar(10))
returns varchar(200)
as
begin

declare @Complications varchar(200), @CompList varchar(300)
set @Complications =
(
select
case when O.Surg_Occl = 1
	then
	case when @CompList is null
		then @CompList + 'Surgical Occlusion'
		else @CompList + ', Surgical Occlusion'
	end
	else @CompList
end,
case when O.Surg_Bleed = 1
	then
	case when @CompList is null
		then @CompList + 'Surgical Bleed'
		else @CompList + ', Surgical Bleed'
	end
	else @CompList
end,
case when O.Surg_Device = 1
	then
	case when @CompList is null
		then @CompList + 'Surgical Device'
		else @CompList + ', Surgical Device'
	end
	else @CompList
end,
case when O.Other_Surg is not null
	then
	case when @CompList is null
		then @CompList + O.Other_Surg
		else @CompList + ', ' + O.Other_Surg
	end
	else @CompList
end

from
Outcomes O

where
@Casenum = O.Casenum
)

return @Complications

end

Using just one varible was returning an error. Also if i eliminate all but one case statement the function will run. Does anyone have any ideas how to fix this or if there is another way to accomplish what i want. I could do the formatting in reporting services but would rather have it done in the stored procedure.

 

[gmmastros]

create function dbo.MorbidityList

    (@Casenum varchar(10))
returns varchar(200)
as
begin
Declare @C Table (Complication VarChar(100))
Declare @Output VarChar(200)

Insert
Into    @C(Complication)
Select  'Surgical Occlusion' 
From    Outcomes
Where   Casenum = @CaseNum
        And Surg_Occl = 1

Union

Select  'Surgical Bleed'
From    Outcomes
Where   CaseNum = @CaseNum
        And Surg_Bleed = 1

Union

Select  'Surgical Device'
From    Outcomes
Where   CaseNum = @CaseNum
        And Surg_Device = 1

Union

Select  Other_Surg
From    Outcomes
Where   CaseNum = @CaseNum
        And Other_Surg Is Not NULL

Select @Output = Coalesce(@Output + ', ', '') +  Complication
From @C

Return @Output
End

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[gmmastros]

I looked at this a little closer. My previous post should work, but will probably be inefficient (especially in a function). If I understand correctly, you want to build a text string with information from a single record in a table. The challenge here is to put commas in the proper place. The code here doesn't bother with the commas until after the string is built. If the first 2 characters of the string are ', ', then take the right part of the string without the first 2 characters. I think you will get better performance by using this function instead.

create function dbo.MorbidityList

    (@Casenum varchar(10))
returns varchar(200)
as
begin
Declare @Output VarChar(200)

Select 	@Output = 
		Case When surg_occl = 1 then 'Surgical Occlusion' Else '' End
		+ Case When Surg_Bleed = 1 then ', Surgical Bleed' Else '' End
		+ Case When Surg_Device = 1 Then ', Surgical Device' Else '' End
		+ Case When Other_Surg Is Not NULL Then ', ' + Other_Surg Else '' End
From	@T
Where	CaseNum = 5

Select @Output = Case When Left(@Output,2) = ', ' Then Right(@Output, Len(@Output)-2) Else @Output End

Return @Output
End

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[jmk418]

thanks for the help the first post did work i will look at both examples and determine the best approach
thanks again

 

[gmmastros]

In the second post (which I think will be faster to execute), I left some debug code.

Change...

From @T
Where CaseNum = 5

To...

From Outcomes
Where CaseNum = @CaseNum

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom