expenguin expenguin - 1 month ago 9
SQL Question

SQL Error: "Only one expression can be specified in the select list when the subquery is not introduced with EXISTS." Not sure how to fix

So I'm trying to get the results of a subquery into a parameter based on if the email address exists in the DB or not. Once it's found, I want it to set the ID of the row found to @ID and delete the row. However, I'm receiving the following error:

"Only one expression can be specified in the select list when the subquery is not introduced with EXISTS."

I've searched high and low for an answer, but I'm coming up empty. What am I doing wrong? How can I fix it?

Feel free to leave any advice on what I could be doing better as well. Thanks!

DECLARE @EmailAddr varchar(255)
DECLARE @ID int
DECLARE @Email_Text varchar(max)

SET @EmailAddr = (SELECT TOP 1 Email FROM tempdb.dbo.WarrantyAnniversaryEmail)
WHILE (SELECT COUNT(Email) FROM tempdb.dbo.WarrantyAnniversaryEmail WHERE Email = @EmailAddr) > 0
BEGIN

SET @Email_Text = @Email_Text + ';' + (SELECT TOP 1 Id, [First] + ' ' + [Last] as FullName, @EmailAddr, Company, Phone, [Address] + ', ' + City + ', ' + [State] + ' ' + Zip as FullAddress
FROM tempdb.dbo.WarrantyAnniversaryEmail
WHERE Email = @EmailAddr)
SET @ID = (SELECT TOP 1 Id FROM tempdb.dbo.WarrantyAnniversaryEmail WHERE Email = @EmailAddr)
DELETE FROM tempdb.dbo.WarrantyAnniversaryEmail WHERE Id = @ID
PRINT @Email_Text
END

Answer

Your problem is obviously this line:

SET @Email_Text = (@Email_Text + ';' +
                   (SELECT TOP 1 Id, [First] + ' ' + [Last] as FullName, @EmailAddr, Company, Phone, [Address] + ', ' + City + ', ' + [State] + ' ' + Zip as FullAddress
                    FROM tempdb.dbo.WarrantyAnniversaryEmail
                    WHERE Email = @EmailAddr
                   )
                  );

The subquery selects multiple columns, starting with id. A subquery used in this context is a "scalar subquery" and it can return only a single column (and at most one row).

I'm not sure what you want to return, so it is hard to make a more concrete suggestion.