Let's create and populate some tables so we have some data to work with. Assume you have a sales effort management (SEM) system that allows you to track the number of sales calls made on a potential client. A sales call is not a phone call, but a get together such as lunch or another type of person-to-person meeting. One of the things the VP of Sales wants to know is how many of his sales personnel participate in a call. The following tables allow you to track this information.

   1: CREATE TABLE Employees
   2: (
   3:     Emp_UniqueID smallint PRIMARY KEY,
   4:     Emp_FName varchar(30) NOT NULL,
   5:     Emp_LName varchar(30) NOT NULL,
   6: )
   7: go
   8:  
   9: CREATE TABLE SalesCalls
  10: (
  11:     SalCal_UniqueID smallint PRIMARY KEY,
  12:     SalCal_Desc varchar(100) NOT NULL,
  13:     SalCal_Date smalldatetime NOT NULL,
  14: )
  15: go
  16:  
  17: CREATE TABLE SalesCallsEmployees
  18: (
  19:     SalCal_UniqueID smallint NOT NULL,
  20:     Emp_UniqueID smallint NOT NULL,
  21: )
  22: go

The SalesCallsEmployees table is a junction table (aka associative table) that relates the employees (sales personnel) to a particular sales call. Let's populate the tables with sample data using these INSERT statements.

INSERT Employees VALUES (1,'Deepankar','Raizada')
INSERT Employees VALUES (2,'Amit','Gupta')
INSERT Employees VALUES (3,'Vikram','Kumar')
INSERT Employees VALUES (4,'Satish','Mishra')
 
INSERT SalesCalls VALUES (1,'Lunch w/ Deepankar Raizada','01/21/08')
INSERT SalesCalls VALUES (2,'Golfing w/ Amit Gupta','01/22/08')
 
INSERT SalesCallsEmployees VALUES (1,1)
INSERT SalesCallsEmployees VALUES (1,2)
INSERT SalesCallsEmployees VALUES (1,4)
INSERT SalesCallsEmployees VALUES (2,2)

The first sales call (Lunch w/ Deepankar Raizada) had three employees participate. Using the old approach, I would have used the code shown here (inside a stored procedure) to build the comma-delimited string. The resultset shows the output when the "Lunch w/ Deepankar Raizada" sales call is edited.

   1: DECLARE @Emp_UniqueID int,
   2: @EmployeeList varchar(100)
   3:  
   4: SET @EmployeeList = ''
   5:  
   6: DECLARE crs_Employees CURSOR
   7: FOR SELECT Emp_UniqueID
   8: FROM SalesCallsEmployees
   9: WHERE SalCal_UniqueID = 1
  10:  
  11: OPEN crs_Employees
  12: FETCH NEXT FROM crs_Employees INTO @Emp_UniqueID
  13:  
  14: WHILE @@FETCH_STATUS = 0
  15: BEGIN
  16:   SELECT @EmployeeList = @EmployeeList+CAST(@Emp_UniqueID AS varchar(5))+ ', '
  17:   FETCH NEXT FROM crs_Employees INTO @Emp_UniqueID
  18: END
  19:  
  20: SET @EmployeeList = SUBSTRING(@EmployeeList,1,DATALENGTH(@EmployeeList)-2)
  21:  
  22: CLOSE crs_Employees
  23: DEALLOCATE crs_Employees
  24:  
  25: SELECT @EmployeeList

This code may look a little complicated, but all it's doing is creating a cursor that holds the Emp_UniqueID values associated with the sales call and processing it with a WHILE to build the string. The important thing for you to note is that this approach takes several lines of code and uses a cursor. In general, cursors are considered evil and should only be used as a last resort.

The new and improved approach can create the same resultset with a single SELECT statement.

   1: DECLARE @EmployeeList varchar(100)
   2:  
   3: SELECT @EmployeeList = COALESCE(@EmployeeList + ', ', '') + 
   4:    CAST(Emp_UniqueID AS varchar(5))
   5: FROM SalesCallsEmployees
   6: WHERE SalCal_UniqueID = 1