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

Question

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

Navigation

#1 by (1 votes)

0

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 goes straight, and it's in Infinite Loop, sending multiple Emails to just 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

sql-server tsql procedure cursor

asked by anonymous 28.10.2015 / 14:35

1 answer

How to use sessions created in php no angularjs (Ionic)? Change directory to HTML understand src destination

score 1 · Answer 1

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

See the snippet below:

SET @_body=
(
    SELECT body FROM Emails 
)

Use the id of the Emails table with where clause to search for the correct data. type

SELECT body FROM Emails where id = 1

Change the sections below with the id 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