need help setting multiple rows to a one varable

[DougP]

I have a table with managers and their employess.
Column Manager has a manager, one to many times for each employee, like so:
Manager employee
Fred Wilma
Fred Pebbles
Fred Dino
Barney Betty
Barney BamBam

I need to set @employess ='Wilma, Pebbles, Rocky' the first time fior Fred and next for Manager Barney the
@employess = 'Betty, BamBam'
I want to email them each Manager and have the 'body' of the email all his employess, so Fred gets an email with 'Wilma, Pebbles, Rocky'
and Barney get an email with 'Betty, BamBam' in the body. I can do the email part, just thaving trouble shoving in more than one item into a variable

	DECLARE @Email  nvarchar(150)
	DECLARE @strSubject nvarchar(200)
	DECLARE @strBody nvarchar(max)
	DECLARE @Name  nvarchar(max)
	DECLARE mycursor CURSOR LOCAL FAST_FORWARD FOR 
	Select  Distinct Email from SOWEmailManagerListResource
			OPEN mycursor  
			  WHILE 1 = 1  
			 BEGIN     
			 FETCH NEXT FROM mycursor    
            INTO @Email
            IF @@FETCH_STATUS <> 0    
			break 
			Else
			Begin
			Set @employee = (Select (LastName +', '+ FirstName) as employee from SOWEmailManagerListResource
				Where Email= @Email)
                           -- need to set @employee to each person under them.
			end
			end
				
  CLOSE mycursor 
  DEALLOCATE mycursor

DougP

 

[johnherman]

You need a second cursor to loop through SELECT (LastName +', '+ FirstName) as employee from SOWEmailManagerListResource
Where Email= @Email)
After you fetch each row, append it to DECLARE employeelist. When the manager e-mail changes, reset employeelist to the null string.


====================================
The reasonable man adapts himself to the world. The unreasonable one persists in trying to adapt the world to himself. Therefore all progress depends on the unreasonable man. - George Bernard Shaw

 

[gmmastros]

I would recommend you use a recursive CTE to dump the relevant data in to a temp table, and then cursor over the temp table to send the emails. The recursive CTE would be similar to the code found here: thread183-1710733



-George
Microsoft SQL Server MVP
My Blogs
SQLCop
twitter
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[DougP]

Ok went down that route:
this one does not have the CTE, But I added the code to put the results in a TEMP table
I still can't get what I need whihc is setting @employess to more that one person

	DECLARE mycursor CURSOR LOCAL FAST_FORWARD FOR 
		--build string of all emails found in above query
	select distinct Email from #TEMP	
			OPEN mycursor  
			  -- Always true  
			 WHILE 1 = 1  
			 BEGIN     
			  -- Get next record from cursor   
			FETCH NEXT FROM mycursor        
            INTO @Email
            IF @@FETCH_STATUS <> 0    
				break 
			Else
				Begin
					Insert SOWEmailManagerListResource
					(ManagerName,Email, ResourceLastName, ResourceFirstName, WeekEndDate)
					Select ManagerName,Email, ResourceLastName, ResourceFirstName, WeekEndDate from #TEMP	
					Where Email = @Email
				end
			end
				  
  CLOSE mycursor 
  DEALLOCATE mycursor 

  
	Select * from SOWEmailManagerListResource

	DECLARE @Email  nvarchar(150)
	DECLARE @strSubject nvarchar(200)
	DECLARE @strBody nvarchar(max)
	DECLARE @Name  nvarchar(max)
	DECLARE mycursor CURSOR LOCAL FAST_FORWARD FOR 
	Select  Distinct Email from SOWEmailManagerListResource
			OPEN mycursor  
			  -- Always true  
			 WHILE 1 = 1  
			 BEGIN     
			  -- Get next record from cursor   
			FETCH NEXT FROM mycursor        
            INTO @Email
            IF @@FETCH_STATUS <> 0    
				break 
			Else
				Begin
				[highlight #8AE234]-- Need to set @employee to each person[/highlight]	
			[highlight #FCE94F]Select (ResourceLastName +', '+ ResourceFirstName) as Name from SOWEmailManagerListResource
					Where Email= @Email[/highlight]
					
				
				end
			end
				
  CLOSE mycursor 
  DEALLOCATE mycursor

DougP