Building a comma-delimited list in stored procedure

[Azathoth]

I'm trying to build a comma-delimited list of test codes from some tables in my database. I don't think the details of the tables are important, because I think there's something wrong with my concatenation statement. test_code is of type char(5), but my variable (@code_list) seems to end up remaining empty. Any ideas?


declare @code_list char(50)

select
@code_list = @code_list + ', ' + rtrim(lutr.test_code)
from
lab_unit lu
inner join lab_unit_test_result lutr
on lu.unit_number = lutr.unit_number and lu.fda_number = lutr.fda_number
inner join tbl_lab_test tlt
on tlt.test_code = lutr.test_code
where
tlt.num_prod_alert != 0
and lu.unit_number = @unit_number
and lu.fda_number = @fda_number

 

[SQLSister]

YOu need to set it equal to something first. WHen you add a null to something else it becomes null.

Questions about posting. See faq183-874

 

[Azathoth]

Even if I do something like this:

declare @code_list char(50)
set @code_list = 'test'

before my select statement, I still end up with only 'test' being contained in @code_list.

 

[SQLSister]

Is your query actually returning any records?

Try it just with setting the variable to the field in the query without the other parts. Is it stillempty?

Questions about posting. See faq183-874

 

[Azathoth]

For some reason it works if I reverse the order of the concatenation arguments:

@code_list = rtrim(lutr.test_code) + ', ' + @code_list

of course then I have to do this:

set @code_list = substring(@code_list, 1, (len(@code_list) - 1))

Regardless, it works. But I'm wondering how I return this string now...return() seems to only like integer values?

 

[ESquared]

• Return, from a procedure, only returns integer values. Declare a variable as OUTPUT and call the stored procedure with OUTPUT as well.

• You would have had to do a substring to get rid of the leading comma, anyway.

• You can avoid an initial set and a substring with

SELECT @code_list = ISNULL(','+@code_list,'') + rtrim(lutr.test_code)

but it might be slightly slower.

• Are you sure there are no nulls in test_code?

• The order of the arguments should make no difference whatsoever.

 

[Azathoth]

The problem is, if I'm not mistaken, that OUTPUT can only be used with parameter variables. @code_list is declared inside the stored procedure as isn't an argument...is there any other way to return it?

 

[SQLSister]

Select @code_list

Questions about posting. See faq183-874

 

[ESquared]

It depends on how you want to use the results.