Public Function GetUserOrganisationIds(ByVal userID As Integer) As String
Dim dbCon As New SqlConnection(ConnectionString)
dbCon.Open()
Dim cmd As New SqlCommand("GetUserOrganisationIds", dbCon)
cmd.CommandType = CommandType.StoredProcedure
Try
Dim dr As SqlDataReader = cmd.ExecuteReader
Dim orgList As New List(Of String)
While dr.Read
orgList.Add(dr("OrganisationID").ToString)
End While
If orgList.Count > 0 Then
Return String.Join(",", orgList.ToArray())
Else
Return ""
End If
Catch ex As Exception
Throw (ex)
Finally
dbCon.Close()
End Try
End Function
Here is how to do it in sql:
--declare holder var
DECLARE @list VARCHAR(8000)
--build comma separated list
SELECT @list = COALESCE(@list + ',', '') + CAST(DZName AS VARCHAR(5) )
FROM ClubDZlocation where ClubId = 201
--show results
SELECT @list AS 'list'
