Need help with a Stored Procedure please

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

General SQL Server Forums

New to SQL Server Programming

Need help with a Stored Procedure please

Author

Topic

ptm82379
Starting Member

6 Posts

Posted - 2013-06-06 : 14:20:14

Thanks in advance for any help.
Please keep in mind Im new with SQL.

Im trying to create a stored procedure and can get one part to work but the second part errors on me.

Here are the errors:
Msg 512, Level 16, State 1, Procedure Chapter_Status_Update, Line 15
Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.

and

Msg 14624, Level 16, State 1, Procedure sp_send_dbmail, Line 242
At least one of the following parameters must be specified. "@body, @query, @file_attachments, @subject".

I can get just the update to work fine. Its when I add the email portion that everything goes south.

I want the chapters table to update each records status field based on calculations

then

I want the email portion to send an email for each chapter that had its status updated to 'Short Term Inactive' while looping through and checking each record in the table checking for the 'short term inactive' status

Here is the code I have so far:

USE [YM]
GO
/****** Object: StoredProcedure [dbo].[Chapter_Status_Update] Script Date: 06/05/2013 14:34:38 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER PROCEDURE
[dbo].[Chapter_Status_Update]

AS

Declare @Today as Date = GETDATE();
Declare @Active as Date = DATEADD(DAY, -426, @Today);
Declare @Closed as Date = DATEADD(DAY, -365, @Today);
Declare @ShortTermInactive as Date = DATEADD(month, -14, @Today);
Declare @LongTermInactive as Date = DATEADD(month, -24, @Today);
Declare @Pending as Date = DATEADD(DAY, -364, @Today);
Declare @Status as nvarchar(50);
Declare @ID as int = (select ID_Chapter from Chapters where ID_Chapter = 1);
Declare @CN as Nvarchar(100) = (select Chapter_name from Chapters);

DECLARE @chapter as varchar(255);
DECLARE @HTMLbody as varchar(255);
DECLARE @heading as varchar(255);

SET @chapter = @CN
SET @HTMLbody = @chapter + ' has gone to a status of Short Term Inactive' + char (13) + char (13) + 'Please contact the advisor to induct more members or log in to the YM website and deactivate this Chapter.'

SET @heading = @chapter + ' status notification'

Update ym.dbo.Chapters
set [status] = 'Active'
where Last_Induction_Date > @Active

Update ym.dbo.Chapters
set [status] = 'Short Term Inactive'
where ym.dbo.Chapters.Last_Induction_Date < @ShortTermInactive and ym.dbo.Chapters.Last_Induction_Date > @LongTermInactive;

Update ym.dbo.Chapters
set [status] = 'Long Term Inactive'
where ym.dbo.Chapters.Last_Induction_Date < @LongTermInactive;

Update ym.dbo.Chapters
set [status] = 'Pending'
where ((Last_Induction_Date is null) and (Date_Accepted > @ShortTermInactive));

Update ym.dbo.Chapters
set [status] = 'Closed'
where ((Last_Induction_Date is null) and (Date_Accepted < @ShortTermInactive)) or ((Last_Induction_Date is null) and (Date_Accepted is Null))

(select * from Chapters where ID_Chapter = @ID and [status] = 'Short Term Inactive')

while (@ID <= 8999)
begin

If EXISTS (select * from Chapters where ID_Chapter = @ID and [status] = 'Short Term Inactive')

--sp_send_dbo is the extended sproc used to send the mail if record exists

exec msdb.dbo.sp_send_dbmail
@profile_name='sql_mail',
@recipients = 'blah',
@copy_recipients = 'blah',
@subject = @heading,
@body = @HTMLbody
set @ID = @ID + 1
end

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2013-06-06 : 15:31:46

That first error message is coming from these two statements

Declare @ID as int = (select ID_Chapter from Chapters where ID_Chapter = 1);
Declare @CN as Nvarchar(100) = (select Chapter_name from Chapters);

The question SQL Server is asking you is "when I do the select, I get multiple rows, and you are asking me to assign it to a single variable. What do you want me to do?"

I am not sure why you are seeing the second error message - it could be that there was no valid data for subject and body because the variables @heading and @HTMLBody did not get populated.

ptm82379
Starting Member

6 Posts

Posted - 2013-06-06 : 15:39:32

Here is my updated code:
USE [YM]
GO
/****** Object: StoredProcedure [dbo].[Chapter_Status_Update] Script Date: 06/05/2013 14:34:38 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER PROCEDURE
[dbo].[Chapter_Status_Update]

AS

Declare @Today as Date = GETDATE();
Declare @Active as Date = DATEADD(DAY, -426, @Today);
Declare @Closed as Date = DATEADD(DAY, -365, @Today);
Declare @ShortTermInactive as Date = DATEADD(month, -14, @Today);
Declare @LongTermInactive as Date = DATEADD(month, -24, @Today);
Declare @Pending as Date = DATEADD(DAY, -364, @Today);
Declare @Status as nvarchar(50);
Declare @ID as int = 1;
Declare @CN as Nvarchar(100) = (select Chapter_name from Chapters);

DECLARE @chapter as varchar(255);
DECLARE @HTMLbody as varchar(255);
DECLARE @heading as varchar(255);

SET @chapter = @CN
SET @HTMLbody = @chapter + ' has gone to a status of Short Term Inactive' + char (13) + char (13) + 'Please contact the advisor to induct more members or log in to the YM website and deactivate this Chapter.'
SET @heading = @chapter + ' status notification'

Update ym.dbo.Chapters
set [status] = 'Active'
where Last_Induction_Date > @Active

Update ym.dbo.Chapters
set [status] = 'Short Term Inactive'
where ym.dbo.Chapters.Last_Induction_Date < @ShortTermInactive and ym.dbo.Chapters.Last_Induction_Date > @LongTermInactive;

Update ym.dbo.Chapters
set [status] = 'Long Term Inactive'
where ym.dbo.Chapters.Last_Induction_Date < @LongTermInactive;

Update ym.dbo.Chapters
set [status] = 'Pending'
where ((Last_Induction_Date is null) and (Date_Accepted > @ShortTermInactive));

Update ym.dbo.Chapters
set [status] = 'Closed'
where ((Last_Induction_Date is null) and (Date_Accepted < @ShortTermInactive)) or ((Last_Induction_Date is null) and (Date_Accepted is Null))

select * from Chapters where ID_Chapter = @ID and [status] = 'Short Term Inactive'

begin
while (@ID <= 8999)
If EXISTS (select * from Chapters where ID_Chapter = @ID and [status] = 'Short Term Inactive')

--sp_send_dbo is the extended sproc used to send the mail if record exists

exec msdb.dbo.sp_send_dbmail
@profile_name='sql_mail',
@recipients = 'blah',
@copy_recipients = 'blah',
@subject = @heading,
@body = @HTMLbody

set @ID = @ID + 1;

end

I just get this error now:

Msg 512, Level 16, State 1, Procedure Chapter_Status_Update, Line 15
Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2013-06-06 : 16:00:28

You didn't change the second line:

Declare @CN as Nvarchar(100) = (select Chapter_name from Chapters);

Declare @CN as Nvarchar(100);
select @CN = Chapter_name from Chapters;

This may not be logically correct, but it will fix the error message you are seeing.

ptm82379
Starting Member

6 Posts

Posted - 2013-06-06 : 16:21:59

I did away with @CN and incorporated a select statement and that error went away but I am still not getting an email when a new record qualifies as short term inactive

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2013-06-06 : 17:30:16

Take a look in msdb.dbo.sysmail_mailitems to see if your mail is getting queued at all. Are you getting any error messages? Is the code in fact going through the exec msdb.dbo.sp_send_dbmail part of your code? You can put a print statement in there and run the stored procedure to see if it infact gets there. If it does get there, print out the values of the parameters such as @HTMLBody to make sure they are not null.

ptm82379
Starting Member

6 Posts

Posted - 2013-06-07 : 07:32:55

Ill give that a shot.

Yes it appears that the code compiles and executes successfully with no errors. It is strictly the send mail portion that isnt working now. Thanks for your help.

ptm82379
Starting Member

6 Posts

Posted - 2013-06-07 : 10:04:30

I was able to get it working.

I had to add another variable to get the email portion to only send one email.

My If statement had to be adjusted in the loop and one of the set statements had to be moved before the while loop.

Thanks for your help in resolving this.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources