SQL Comma Delimit Problem

I have a small prob开发者_运维知识库lem. I am getting this error, and I would appreciate if anyone could help me out! All I am trying to do is a comma delimited list.

SET NOCOUNT ON;

DECLARE @TypeID Varchar(250)

SELECT
    @TypeID = COALESCE(@TypeID +',' ,'') + TypeID 
FROM
    Related

RETURN @TypeID 

Conversion failed when converting the varchar value '8,' to data type int.

Does anyone know how to fix, please? I've tried CONVERT(VARCHAR, @TypeID) but this doesn't seem to make any difference!

Is the field TypeId in the related table an integer? You would need to cast the database field to a varchar for your code to work

Select @typeId = coalesce(@typeId+',','') + cast(typeId as varchar(20))
from related

The other answers are correct that you need to CAST the int to varchar but the other problem is you are using RETURN @TypeID.

The return statement expects an integer (usually used to indicate success/failure not for data) and can not be used with strings. You need to use an OUTPUT parameter or a SELECT statement instead.

Cast TypeId as a varchar:

DECLARE @TypeID Varchar(250)

SELECT
    @TypeID = COALESCE(@TypeID + ',' , '') + CAST(TypeID As varchar(20))
FROM
    Related

RETURN @TypeID 

Another method would be to use string concactination:

Declare @TypeId varchar(250)
Select @TypeId = ''

Select @TypeId = @TypeId + ', ' + CAST(TypeID As varchar(20))
From Related

Try following :

DECLARE @TypeID Varchar(250)

SET @TypeID=''

SELECT @TypeID = COALESCE(@TypeID + ',' , '') + CAST(TypeID As varchar(20)) FROM Related

SELECT STUFF(@TypeID , 1, 1, '')