Working with recordsets inside a stored procedure

• ctricaud

  Valued Member

  Points: 62

  More actions

  January 10, 2006 at 7:17 am

  #188840

  I would like to create a stored procedure that will send an email to all people reaching a special condition.

  Opening the recordset is not difficult. Neither to send an email with sql 2005. Thus if I have more than one row, I don't know how I can get the email of all the people I want to reach. I tried to use a cursor, but without success, since I can only update or delete the row.

  The problem is simple, how I can get in a variable all the emails I need?

  Thanks for your help...

  Chris, Paris

• ctricaud

  Valued Member

  Points: 62

  More actions

  January 10, 2006 at 7:54 am

  #613808

  Ok I found the solution, it was too obvious. I had to use the Into clause in the Fetch command...

  Maybe it can help someone?

  USE

  dbAffinite

  GO

  SET

  NOCOUNT ON

  -- On déclare les variables utilisées

  DECLARE

  contact_cursor CURSOR FOR

  SELECT

  ProfilEmail FROM Pro_Profil

  WHERE

  profildatecreation<dateadd(day,+2,Getdate())

  DECLARE

  @email varchar(100);

  -- On ouvre le curseur

  OPEN

  contact_cursor

  -- Perform the first fetch.

  FETCH

  NEXT FROM contact_cursor INTO @email

  -- Check @@FETCH_STATUS to see if there are any more rows to fetch.

  WHILE

  @@FETCH_STATUS = 0

  BEGIN

  -- On envoie l'email

  EXEC msdb.dbo.sp_send_dbmail

  @profile_name

  = 'Plus-Si-Affinites',

  @recipients

  = @email,

  @body

  = 'Essai d''un envoi d''email',

  @subject

  = @email;

  FETCH NEXT FROM contact_cursor INTO @Email

  END

  CLOSE

  contact_cursor

  DEALLOCATE

  contact_cursor

  GO