loop records in SP?? - 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 2005 Forums

Transact-SQL (2005)

loop records in SP??

Author

Topic

helixpoint
Constraint Violating Yak Guru

291 Posts

Posted - 2008-09-17 : 12:59:01

I have an asp.net app that puts records in a table of emails to be sent out. I am going to try to use SQL Server agent to loop through the records (fields in table recipient, body) and send the emails out. How do I loop through the records and send the emails using the sp_send_dbmail proc

EXEC msdb.dbo.sp_send_dbmail @profile_name='name',

@recipients='email@email.com',

@subject='Test message from database',

@body='This is the body of the test message. This was sent by sql server 2005.'

Dave
Helixpoint Web Development
http://www.helixpoint.com

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-09-17 : 13:06:56

Use dynamic sql.something like


DECLARE @ID int,@Sql varchar(1000),@Email varchar(100)
SELECT @ID=MIN(PK)
FROm EmailTable

WHILE @ID IS NOT NULL
BEGIN
SELECT @EMail= Email
FROM EmailTable
WHERE PK=@ID

SET @Sql='EXEC msdb.dbo.sp_send_dbmail @profile_name=''name'',

@recipients='+ @EMail+',

@subject=''Test message from database'',

@body=''This is the body of the test message. This was sent by sql server 2005.'''
EXEC (@Sql)
SELECT @ID=MIN(PK)
FROm EmailTable
WHERE PK>@ID
END

helixpoint
Constraint Violating Yak Guru

291 Posts

Posted - 2008-09-17 : 14:06:41

AWSOME, but the body of the email in in that same table with the email, How do I do that?

Dave
Helixpoint Web Development
http://www.helixpoint.com

helixpoint
Constraint Violating Yak Guru

291 Posts

Posted - 2008-09-17 : 15:28:51

I am getting the following error

Executed as user: NT AUTHORITY\SYSTEM. Incorrect syntax near '.'. [SQLSTATE 42000] (Error 102). The step failed.

Dave
Helixpoint Web Development
http://www.helixpoint.com

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2008-09-17 : 16:22:40

>>AWSOME, but the body of the email in in that same table with the email, How do I do that?
just declare an additional "@body" parameter and populate it at the same time you get @email

>>Executed as user: NT AUTHORITY\SYSTEM. Incorrect syntax near '.'. [SQLSTATE 42000] (Error 102). The step failed.
post your code

Be One with the Optimizer
TG

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources