I want get employeelist in one single string.
now in new pl/sql version having the COALESCE function,
The magic of function is, it will automatically add "," seprator of employeelist.
///////////////////////////Before COALESCE ///
DECLARE @Emp_UniqueID int,
@EmployeeList varchar(100)
SET @EmployeeList = ''
DECLARE crs_Employees CURSOR
FOR SELECT Emp_UniqueID
FROM SalesCallsEmployees
WHERE SalCal_UniqueID = 1
OPEN crs_Employees
FETCH NEXT FROM crs_Employees INTO @Emp_UniqueID
WHILE @@FETCH_STATUS = 0
BEGIN
SELECT @EmployeeList = @EmployeeList+CAST(@Emp_UniqueID AS varchar(5))+ ', '
FETCH NEXT FROM crs_Employees INTO @Emp_UniqueID
END
SET @EmployeeList = SUBSTRING(@EmployeeList,1,DATALENGTH(@EmployeeList)-2)
CLOSE crs_Employees
DEALLOCATE crs_Employees
SELECT @EmployeeLis
Output:
1, 2, 4
///////////////////////////After COALESCE/////////////
DECLARE @EmployeeList varchar(100)
SELECT @EmployeeList = COALESCE(@EmployeeList + ', ', '') +
CAST(Emp_UniqueID AS varchar(5))
FROM SalesCallsEmployees
WHERE SalCal_UniqueID = 1
SELECT @EmployeeList
Output:1,2,4
/////////////////////////////////////////////////////////////////////////////
