DeveloperBarn Forums

DeveloperBarn

Programming & IT forum

If statement stored procedure

This is a discussion on If statement stored procedure within the SQL Development forums, part of the Databases category; Yeah, I tried it without the conversion, (see below) and I get no error's, but the email does go NOW. ...

Go Back   DeveloperBarn Forums > Databases > SQL Development

  #31  
Old May 23rd, 2008, 09:25 AM
Barn Enthusiast
 
Join Date: Mar 2008
Posts: 207
Rep Power: 2
peebman2000 is on a distinguished road
Question reply

Yeah, I tried it without the conversion, (see below) and I get no error's, but the email does go NOW. I changed the dates in the 3 records to match today's date and it should have sent the email like it did before. But of course I now selecting the email address and date now.

But I"m going to put the conversion part back in and try it again. I'm looking on line to see if there is something about that datetime conversion error.

Any other ideas let me know.

Code:
USE [peebman]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER PROCEDURE [dbo].[sp_proc_happyBirthday2] 
AS 
SET NOCOUNT ON 
declare @birth datetime, @email varchar(150)

declare row cursor for
select email, birth from email where birth = getdate()

open row

fetch row into @birth, @email

while @@fetch_status = 0

begin
	fetch row into @birth, @email
IF  @birth = getdate()
	
	Begin
        declare @body1 varchar(100)
		
        set @body1 = 'You are reminded to finish your PDQ!'---'Server         EXEC msdb.dbo.sp_send_dbmail 
		
        @recipients = @email,     
        @subject = 'My Mail Test',
        @body = @body1,
        @body_format = 'html';
	end 
else if @birth <> getdate()
BEGIN
	EXEC msdb.dbo.sysmail_stop_sp	
	End
end
close row

Deallocate row

return
Quote:
Originally Posted by jmurrayhead View Post
Not sure what's causing the error all of a sudden. Have you tried it again without the conversion to see if that is actually the issue?
Reply With Quote
  #32  
Old May 23rd, 2008, 03:15 PM
Barn Enthusiast
 
Join Date: Mar 2008
Posts: 207
Rep Power: 2
peebman2000 is on a distinguished road
Smile reply

Okay Jmurrayhead, I finally got everything to work. I appreciate the links you sent me they help as well, but it works now. I can schedule for reminder emails to be sent out using sql 2005 job scheduler.

Here is my final stored procedure:
Code:
USE [peebman]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER PROCEDURE [dbo].[sp_proc_happyBirthday2] 
AS 
SET NOCOUNT ON 
exec msdb.dbo.sysmail_start_sp
declare @birth varchar(15),
@email varchar(8000)
set @email =''
declare row cursor for
select email, birth from email where birth = CONVERT(varchar(15), GETDATE(), 101)
-- = convert(varchar(8), getdate(), 112)

open row

fetch row into @email, @birth

while @@fetch_status = 0

begin
	fetch row into @email, @birth
IF @birth = CONVERT(varchar(15), GETDATE(), 101)
	
	Begin
        declare @body1 varchar(100)
		
        set @body1 = 'You are reminded to finish your PDQ!'---'Server 
        EXEC msdb.dbo.sp_send_dbmail 
		
        @recipients = @email,        --@BCC = @BCCList,
        @subject = 'My Mail Test',
        @body = @body1,
        @body_format = 'html';
	end 
else if  @birth <> CONVERT(varchar(15), GETDATE(), 101)
BEGIN
	EXEC msdb.dbo.sysmail_stop_sp	
	End
end
close row

Deallocate row

return
I added some more msdb.dbo.sysmail code and I changed the birth(datecolum) to a varchar to get rid of the "Conversion datetime to character string" error.

And I converted getdate to a varchar to match the birth(datecolumn) in the table.

That worked as well as the msdb.dbo.sysmail_start_sp code to shoot the email.

also before in the begin of loop I had:
Code:
fetch row into @birth, @email
well the select statment selects email, birth not birth, email so I changed it to:
Code:
fetch row into @email, @birth
and i think that helped as well.

So thinks again for the help, sorry for all the postings. Hopfully this post will help other programmers out who want to sent reminder emails using SQL 2005 instead of using executables on the server.

Thanks again
Reply With Quote
  #33  
Old May 23rd, 2008, 03:54 PM
jmurrayhead's Avatar
The Barnfather
 
Join Date: Mar 2008
Real name: Jason
Location: Washington, D.C.
Posts: 1,964
Blog Entries: 8
Rep Power: 15
jmurrayhead has much to be proud ofjmurrayhead has much to be proud ofjmurrayhead has much to be proud ofjmurrayhead has much to be proud ofjmurrayhead has much to be proud ofjmurrayhead has much to be proud ofjmurrayhead has much to be proud ofjmurrayhead has much to be proud ofjmurrayhead has much to be proud of
Default

Glad you were able to figure out the rest. Don't worry about all the postings, that's what this place is for
__________________
jmurrayhead
If you agree with me... click the icon!
If my post solved your problem, click the button in the lower right-hand corner of the post.

If you like it here...throw us a few bones to help
support us.

Join our Folding team: DeveloperBarn Folding

Reply With Quote
Reply

  DeveloperBarn Forums > Databases > SQL Development

Bookmarks

Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


LinkBacks (?)

LinkBack to this Thread: http://www.developerbarn.com/sql-development/206-if-statement-stored-procedure.html

Posted By For Type Date
how to create a stored procedure to send mail xp_send_mail...? - ASP.NET Forums This thread Refback December 23rd, 2008 08:19 AM
how to create a stored procedure to send mail xp_send_mail...? - ASP.NET Forums This thread Refback December 17th, 2008 05:14 AM
how to create a stored procedure to send mail xp_send_mail...? - ASP.NET Forums This thread Refback December 16th, 2008 06:53 AM

Similar Threads

Thread Thread Starter Forum Replies Last Post
Dynamic Stored Procedure jmurrayhead Microsoft SQL Server 16 March 26th, 2008 11:19 AM
Permissions on Tables, Stored Procedures, etc. theChris Microsoft SQL Server 2 March 24th, 2008 11:49 AM


All times are GMT -4. The time now is 02:35 AM.


Copyright ©2008-2010, DeveloperBarn

Content Relevant URLs by vBSEO 3.3.2