Passing Multiple Values to One Parameter

Question

Passing Multiple Values to One Parameter

Gail-378561

SSCommitted

Points: 1602
More actions
June 25, 2009 at 3:15 pm

#211377

I have a stored procedure that runs and checks the DB for weekly workers for scheduling.
Then Emails the people who are scheduled to work the following week.
I can get that to run,send the email but it is very basic as far as info provided in the email.
I want to do send a more detailed email to the users. One that will List all the Workers
that are also scheduled for the week.
So the email would say something like you are schedule to work next week 7/5/2009
These people are also scheduled with you
Joe Jones Sunday AM
Mike Johnson Sunday PM
Sarah Jackson Wednesday PM
I tried to pass this to MY @Workers parameters, but I keep getting an error
Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.
I understand what it's telling me basically my parameter can only accept one value from the query.
How can I do this so my parameters hold all the values?
I thought maybe I could return an Array or temp table something but I don't know the best way to proceed with this.
Here is my sproc that gives me the error.
ALTER PROCEDURE [dbo].[uspWeeklyEmailsSend]
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
Declare @NextDate as SmalldateTime,
@WorkDay as VarChar (11),
@WorkersEmail as Varchar (30),
@WorkersName as Varchar (30),
@WorkersDay as Varchar (10),
@Workers as Varchar (4000),
@Text as VarChar (200),
@EmailText as VarChar (4000)
set datefirst 1
Set @NextDate = (select dateadd(day,3,getdate()))
Set @WorkDay = (select Substring(Convert(varchar, @NextDate,101),0,11))
Set @WorkersName = (Select [Name] from vwWorkerWeek where weeknum = (SELECT dbo.WeekOfMonth(@NextDate)))
Set @WorkersEmail = (Select [Email] from vwWorkerWeek where weeknum = (SELECT dbo.WeekOfMonth(@NextDate)) )
Set @WorkersName = (Select [Day] from vwWorkerWeek where weeknum = (SELECT dbo.WeekOfMonth(@NextDate)) )
Set @Workers = @WorkersName + ' ' + @WorkersDay + ' ' + @WorkersEmail
Set @Text= (Select 'You are Scheduled for Work Duty for Next Week - ' + @WorkDay +
'Please review the list below, If you cannot fulfill your obligation this week please make arrangments by
finding a replacement worker for you this week. The Schedule for This week is a Follows' + @Workers)
Set @EmailText = @Text + ' ' + @Workers
DECLARE @email VARCHAR(4000)
SET @email = ''
--SELECT
set datefirst 1
select @email = @email + (email) --+ ';',(dateadd(day,10,getdate())) as NextDate
from vwWorkerWeek
where weeknum = (SELECT dbo.WeekOfMonth(@NextDate))
exec msdb..Sp_send_dbmail
@profile_name = 'MyProfile',
@recipients = @email,
@subject = 'Upcoming Schedule',
@body = @EmailText
END
Also is there any way to change the From email that is displayed?
For instance I send the email with my email profile that has an email someone@somewhere.com
When the user gets the email I want him to have the from say
NoReply@Somewhere.com. If anything else maybe the reply to could be changed.
I didn't see FROM or Reply To as parameters in the Sp_send_dbmail
Thanks

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Carleton SSCommitted Points: 1706 More actions · Answer 1

When I want to retrun multiple rows into one string variable, I always use the coalesce function. In fact, I do this in several instances dealing with email as well. Looking at your code briefly, give something like this a try:

declare @Workers varchar(4000)

select @Workers = coalesce(@Workers, '') + [name] + ' ' + [Day] + char(10)

from vwWorkerWeek where weeknum = (SELECT dbo.WeekOfMonth(@NextDate))

If you are sending the email to multiple people, you can use this same technique to get all the emails into one string that can be passed to the sp_send_dbmail like this:

declare @email_to varchar(4000)

select @email_to = coalesce(@email_to, '') + userid + '@domain.com; '

from userid_table

Cheers,

Carleton

Gail-378561 SSCommitted Points: 1602 More actions · Answer 2

Thanks that worked great, The workers were not seperated by a carriage return like I had hoped. I thought the + char(10)

like you had it would work but my results still look something like

workername SundayAM WorkerNAme2 SundayPM

I would llike it to be like

WorkerName SundayAM

WorkerNAme SundayPM...

Also anyway you know of to change the from and or reply to field for the email?

Thanks

Carleton SSCommitted Points: 1706 More actions · Answer 3

To get the carriage return try a couple things:

try char(10) + char(10) --Wierd, but sometimes adding two carriage returns = 1 carriage return in an email body

try char(13) + char(13)

You can change the "TO" in your email simply by setting the parameter

@recipients = @email_to.

The only way to change the "FROM" is by setting up another mail profile and speficity that new profile in the sp_send_dbmail parameters:

@profile_name = 'NewProfile'

Carleton SSCommitted Points: 1706 More actions · Answer 4

If your email is set to decipher html tags, to get the carriage return you may also try adding:

'
' + '
'

Carleton SSCommitted Points: 1706 More actions · Answer 5

The last post read my html tags. Any way, you can try adding the break html tag as a string.