asked
Sending Emails via Stored Proc
I have to write a stored procedure to query Table A and for every row returned, update table B and simultaneously send out an email to the user. Any help on how this can be done would be appreciated. Thanks! Shiva Prasad
what type of data is in Table A that has to go to Table B? By "simultaneously" do you mean a seperate email for each row or an email of the rows updated in Table B? This will go to one single user or email address, correct?
I believe this should do it, or get you very very close. You can accomplish the task your trying to do by creating a cursor that executes designated tasks based on every item you put into it, in this case every row from Table_A. Hope this helps. DECLARE @FieldLoop
CURSOR,
@Table_A_key AS VARCHAR(255),
@Name AS VARCHAR(255),
@Email AS VARCHAR(255),
@sql01 AS NVARCHAR(MAX),
@EmailBody AS VARCHAR(255)
SET @FieldLoop = CURSOR
FOR SELECT DISTINCT Table_A_key
FROM Table_A
OPEN @FieldLoop
FETCH NEXT FROM @FieldLoop INTO @Table_A_key
WHILE @@FETCH_STATUS = 0
BEGIN
SET @Name = (SELECT Table_A_Name
FROM Table_A
WHERE Table_A_key = @Table_A_key)
SET @Email = (SELECT Table_A_Email
FROM Table_A
WHERE Table_A_key = @Table_A_key)
SET @sql01 = ' UPDATE Table_B SET Table_B_Name = ''' + @Name + ''' Table_B_Email = ''' + @Email + ''' WHERE Table_B.Table_A_Key = ''' +
@Table_A_key + ''' '
EXEC Sp_executesql @sql01
SET @EmailBody = 'This message is a confirmation that the procedure to update Table B has run for name:' + @Name +
' associated with your email address:' + @Email
EXEC msdb.dbo. Sp_send_dbmail
@recipients = @Email,
@subject = 'Table B has been updated',
@body = @EmailBody
FETCH NEXT FROM @FieldLoop INTO @Table_A_key
END
CLOSE @FieldLoop
DEALLOCATE @FieldLoop
@Table_A_key AS VARCHAR(255),
@Name AS VARCHAR(255),
@Email AS VARCHAR(255),
@sql01 AS NVARCHAR(MAX),
@EmailBody AS VARCHAR(255)
SET @FieldLoop = CURSOR
FOR SELECT DISTINCT Table_A_key
FROM Table_A
OPEN @FieldLoop
FETCH NEXT FROM @FieldLoop INTO @Table_A_key
WHILE @@FETCH_STATUS = 0
BEGIN
SET @Name = (SELECT Table_A_Name
FROM Table_A
WHERE Table_A_key = @Table_A_key)
SET @Email = (SELECT Table_A_Email
FROM Table_A
WHERE Table_A_key = @Table_A_key)
SET @sql01 = ' UPDATE Table_B SET Table_B_Name = ''' + @Name + ''' Table_B_Email = ''' + @Email + ''' WHERE Table_B.Table_A_Key = ''' +
@Table_A_key + ''' '
EXEC Sp_executesql @sql01
SET @EmailBody = 'This message is a confirmation that the procedure to update Table B has run for name:' + @Name +
' associated with your email address:' + @Email
EXEC msdb.dbo. Sp_send_dbmail
@recipients = @Email,
@subject = 'Table B has been updated',
@body = @EmailBody
FETCH NEXT FROM @FieldLoop INTO @Table_A_key
END
CLOSE @FieldLoop
DEALLOCATE @FieldLoop
