Subquery returned more than 1 value. This is not permitted when the subquery Follows =, != , <, <= , >, >=

Question

I have a Procedure that sends email with the table data, including the email that will be forwarded,but it is sending to only the 1 record, the second it passes straight, and she is in Loop Infinity, sending several Emails to only the 1st record

PROCEDURE:

USE [Testes]
GO
/****** Object:  StoredProcedure [dbo].[uspEnviarEmail]    Script Date: 28/10/2015 11:05:18 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[uspEnviarEmail]

AS
BEGIN

 DECLARE  @_profileName VARCHAR(10)
   DECLARE  @_recipients VARCHAR(30)
    DECLARE @_body VARCHAR(500)
DECLARE @_subject VARCHAR(20)
DECLARE @Contador AS INT = 0

DECLARE CursorEmails CURSOR FOR

SELECT 
    profileName,
    recipients,
    body,
    subjectEmail
    FROM
    Emails
OPEN CursorEmails

FETCH NEXT FROM CursorEmails INTO @_profileName,@_recipients,@_body,@_subject

WHILE @@FETCH_STATUS = 0

BEGIN
SET @Contador = @Contador +1

SET @_profileName=
(
    SELECT profileName FROM Emails 
)

SET @_recipients=
(
    STUFF((SELECT';'+RTRIM(LTRIM(recipients))  FROM Emails FOR XML PATH('')),1,1,'')
)

SET @_body=
(
    SELECT body FROM Emails 
)
SET @_subject=
(
    SELECT subjectEmail FROM Emails 
)


EXEC msdb.dbo.sp_send_dbmail

@profile_name = @_profileName,
@recipients = @_recipients,
@body = @_body,
@subject = @_subject
END
FETCH NEXT FROM CursorEmails INTO @_profileName,@_recipients,@_body,@_subject
END

Answer 1

Hello, the return error refers to a query that returns more than one value when the expected is only 1.

see the section below :

SET @_body=
(
    SELECT body FROM Emails 
)

use the table id Emails with Where clause to search the correct data. type

SELECT body FROM Emails where id = 1

Change the sections below with the id ta table...

BEGIN
 declare  @IdEmail int
 DECLARE  @_profileName VARCHAR(10)

 DECLARE CursorEmails CURSOR FOR
 SELECT 
    IdEmail,
    profileName,
    recipients,
    body,
    subjectEmail
    FROM
    Emails


 FETCH NEXT FROM CursorEmails INTO @IdEmail, @_profileName,@_recipients,@_body,@_subject

 FETCH NEXT FROM CursorEmails INTO @IdEmail, @_profileName,@_recipients,@_body,@_subject