Dynamic Select Statement, place result into variable

I've done some searching and nothing comes up with what I'm looking for exactly. I've read about EXEC(@variable) and this MAY be what I want but I can't get it to work.

The idea is, there are multiple groups that I will be sending emails to based on a location something happened at. So I need to store this information into a variable that I can then开发者_开发知识库 pass to @recipients in sp_send_dbmail.

I was using a case for this which was fine, but now we're adding in the functionality that we only want to send the email to certain users based on which shift they were working. For example, 3rd shift doesn't want to know about an issue that happened for shift 1.

To do this, I set up another case to declare a variable based on shift, and removed my original case and created a configuration table for shift a, b, c, etc. So what I'm looking to do is

SELECT @shift FROM table WHERE location this defect happened at.

here is what I was trying to do which is not working.

SET @EmailRecipients = 'SELECT' +  @EmailRecipientsCase + 'FROM ScrapReasonProcessGroups WHERE ID = @ProcessCounter'
SET @EmailRecipients = EXEC(@EmailRecipients)

Use a CASE without dynamic SQL?

SELECT @EmailRecipients = CASE @EmailRecipientsCase
                              WHEN 'shifta' THEN ShiftA
                              WHEN 'shiftb' THEN ShiftB
                              WHEN 'shiftc' THEN ShiftC
                          END
FROM ScrapReasonProcessGroups
WHERE ID = @ProcessCounter

Assuming you're going to get more than one email address back from that query, you'd want to store those results in a temp table.

CREATE TABLE #tmpEmail (
    EmailAddress varchar(200)
)

SET @EmailSQL = 'SELECT ' +  @EmailRecipientsCase + ' FROM ScrapReasonProcessGroups WHERE ID = ' + @ProcessCounter

INSERT INTO #tmpEmail
    (EmailAddress)
    EXEC (@EmailSQL)