Stored Procedure Help - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Stored Procedure Help

Author

Topic

rayfusion
Starting Member

4 Posts

Posted - 2005-11-03 : 16:42:23

I created a storedprocedure that needs the ability to select email addresses from a table and then email each address. It works only if the table has one email address.
If more than one email address exists, I get back the following error message: "Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression."

Here is my code. Please let me know what I am doing wrong. Thanks.

AS

WHILE ( SELECT reminderDate FROM Reminder ) = '11/03/2005'
BEGIN
DECLARE @EmailAddress AS NVARCHAR ( 50 );
--DECLARE @EmailAddress AS NVARCHAR;
--DECLARE @EmailAddress AS NVARCHAR;

Select @EmailAddress = (select manageremail from managerinfo
INNER JOIN Reminder ON (managerinfo.managerid = Reminder.managerid and Reminder.userID = 1))

exec master..xp_sendmail @EmailAddress,'This is a Test'
return -999

END
GO

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2005-11-03 : 16:44:31

Your problem is here:
WHILE ( SELECT reminderDate FROM Reminder ) = '11/03/2005'

How many rows in the Reminder table?

You also need to change your query into:
Select @EmailAddress = manageremail
from managerinfo
INNER JOIN Reminder
ON managerinfo.managerid = Reminder.managerid and Reminder.userID = 1

But you need to loop through this result set and set @EmailAddress during each loop.

Tara Kizer

nosepicker
Constraint Violating Yak Guru

366 Posts

Posted - 2005-11-03 : 21:03:58

Actually, I would say that it is better to leave your query as:

Select @EmailAddress = (select manageremail from managerinfo
INNER JOIN Reminder ON (managerinfo.managerid = Reminder.managerid and Reminder.userID = 1))

because when you get that error message that says "Subquery returned more than 1 value ...", it is telling you that your INNER JOIN is returning multiple values of "manageremail" based on your JOIN conditions. This is a helpful error message. If you did this:

Select @EmailAddress = manageremail from managerinfo
INNER JOIN Reminder ON (managerinfo.managerid = Reminder.managerid and Reminder.userID = 1)

you wouldn't get an error message and you would be getting potentially unexpected results, because SQL Server would simply assign the first value of "manageremail" that it finds to the variable "@EmailAddress". You are probably expecting to get only one result here, so the error message tells you that you need to modify your JOIN or clean up your data.

rayfusion
Starting Member

4 Posts

Posted - 2005-11-04 : 08:04:23

In the reminder table, there can possibly be more then one record. I want to be able to email each emaill address that is in this table. How can I get the storeprocedure to find the first record, send an email to the email address and then go to the next record found, send an email, etc... continuing until there are no more records found.

Thanks.

X002548
Not Just a Number

15586 Posts

Posted - 2005-11-04 : 10:10:01

quote:
Originally posted by tduggan

Your problem is here:
WHILE ( SELECT reminderDate FROM Reminder ) = '11/03/2005'
...
Tara Kizer

How about...your problem is everywhere...this is what you need....


CREATE PROC mySproc99 (
	@ReminderDate datetime
AS
  BEGIN
	DECALRE @ManagerEmail varchar(255)
	DECLARE myCursor99 CURSOR FOR
		SELECT ManagerEmail 
		  FROM ManagerInfo  i
		  JOIN Reminder     r
		    ON i.ManagerId = r.ManagerID
		 WHERE DATEDIFF(d,r.Reminder,@ReminderDate)=0
		   AND r.UserId = 1
	OPEN myCursor99
	FETCH NEXT FROM myCursor99 INTO @ManagerEmail
	WHILE @@FETCH_STATUS = 0
	  BEGIN
		exec master..xp_sendmail @EmailAddress,'This is a Test'
		FETCH NEXT FROM myCursor99 INTO @ManagerEmail
	  END

	CLOSE myCursor99
	DEALLOCATE myCursor99
  END

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

rayfusion
Starting Member

4 Posts

Posted - 2005-11-08 : 11:25:09

X002548, thanks I got it to work. I have another question. I am trying to grab other fields from another table. How can I implement those fields to this storedproc? How would I go about adding the new fields to the cursor? Would I have to create a new cursor for each new field? Here is an example of the new query I want to use:

SELECT ManagerEmail, userName
FROM ManagerInfo i
JOIN Reminder r
ON i.ManagerId = r.ManagerID
JOIN userInfo a
ON a.ManagerId = r.ManagerID
WHERE a.userID = r.userID and DATEDIFF(d,r.ReminderDate,GetDate())=0

Thanks again..

X002548
Not Just a Number

15586 Posts

Posted - 2005-11-08 : 11:53:22

You just substitute the query that'as in the cursor now, and add an extra local variable for the fetch....

Am I missing something?

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

rayfusion
Starting Member

4 Posts

Posted - 2005-11-09 : 09:29:24

Great! I got it to work!

Question?
When using xp_sendmail, is there a way to be able us @emailname and 'static text' together?
I tried to add the var @UserName in the subject and I get back an error message.
EXEC MASTER..xp_sendmail @recipients = @ManagerEmail,
@subject = ' Todays Time Off Schedule',
@copy_recipients = '',
@message = @UserName

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2005-11-09 : 11:44:34

Yes you can @emailname and 'static text' together, but you'll need to do that prior to running xp_sendmail.

DECLARE @s varchar(400)

SET @s = @emailname + 'static text'

EXEC MASTER..xp_sendmail @recipients = @ManagerEmail,
@subject = @s,
@message = @UserName

Tara Kizer

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources