Subquery returned more than one value - not permitted
-
I have a small table that I need to iterate through, grab email addys from and then send an xp_sendmail. Trouble is, it gets through 2 of the twelve rows , and then blows up. Not sure what to make of the errors. Troublesome because it works for first two and not others. Error and Code below:
Error~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression. [SQLSTATE 21000] (Error 512) Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression. [SQLSTATE 21000] (Error 512).
Code~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
DECLARE free_reg CURSOR FOR
SELECT *
FROM temp_Free2
DECLARE @GridDude float,
@NumbReg int,
@fname varchar(38),
@lname varchar(38),
@accnum numeric(9),
@recipients varchar(38),
@EventID varchar(38),
@DateTime datetime
SET @GridDude = (SELECT GRIDDude FROM temp_Free2)
SET @NumbReg = (SELECT NumbReg FROM temp_Free2)
SET @fname = (SELECT fname FROM temp_Free2)
SET @lname = (SELECT lname FROM temp_Free2)
SET @accnum = (SELECT accnum FROM temp_Free2)
SET @recipients = (SELECT email FROM temp_Free2)
OPEN free_reg
FETCH NEXT FROM free_reg INTO @GridDude, @NumbReg, @fname, @lname, @accnum, @recipients, @EventID, @DateTime
WHILE @@FETCH_STATUS = 0
BEGIN
FETCH NEXT FROM free_reg INTO @GridDude, @NumbReg, @fname, @lname, @accnum, @recipients, @EventID, @DateTime
exec master..xp_sendmail @recipients, 'Thank you, Please contact us at','','','','','DO NOT REPLY - Your Group Now Qualifies for a Free Couples Registration!',''
END
INSERT INTO fl_events_grpcrd
SELECT * FROM temp_free2
CLOSE free_reg
DEALLOCATE free_reg
GO
-
The problem is in your SET @GridDude = (SELECT....) section. All of those SELECTs in your SET statements have the ability to return more than one value. You cannot assign more than one value to a variable. Get rid of the entire SET @..... section. You're cursor will handle assigning the values into the variables.
This should get you started. I think you've got other problems with your cursor, but I'll let you take first stab at them.
-
I toned this down from sending an email to just grabbing the one column (email address) and populating a table to get to the core of the problem.
I took your advice and got rid of the SET...SELECT Section. This raised another error, as you might have suspected...still at odds figuring this out though.
~~~~~~~~~~~~ERROR
Server: Msg 16924, Level 16, State 1, Procedure sp_events_grpcrd4, Line 18
Cursorfetch: The number of variables declared in the INTO list must match that of selected columns.
~~~~~~~~~~~~~CODE
DECLARE free_reg CURSOR FOR
SELECT *
FROM temp_Free2
DECLARE
@recipients varchar(38)
OPEN free_reg
FETCH NEXT FROM free_reg INTO @recipients
WHILE @@FETCH_STATUS = 0
BEGIN
FETCH NEXT FROM free_reg INTO @recipients
INSERT INTO temp_free_test
VALUES (@recipients)
END
CLOSE free_reg
DEALLOCATE free_reg
GO
-
Your cursor declaration needs to select a single column - EmailId. You cannot select all column into a single variable.
-
You have declared your cursor to be a SELECT *, but your FETCH is only using the e-mail. Change your cursor declaration to be SELECT email from temp_free2.
P.S. - I don't think your 100% out of the woods yet.
-
But you're definitely over the river.
It appears that your inner FETCH is out of place. You're doing two fetches before the first insert. Typically, when the first fetch is outside of the loop like that, the inner fetch happens at the end of the loop.
Also, if temp_free_test has more than one column, you'll need to specify the appropriate column in that INSERT.
-
DECLARE
free_reg CURSOR FOR
SELECT GRIDDude,
NumbReg
,
fname
,
lname
,
accnum
,
email
,
recipients
,
eventid
,
[datetime]
FROM temp_Free2
DECLARE
@GridDude float,
@NumbReg
int,
@fname
varchar(38),
@lname
varchar(38),
@accnum
numeric(9),
@recipients
varchar(38),
@EventID
varchar(38),
@DateTime
datetime
OPEN
free_reg
FETCH
NEXT FROM free_reg INTO @GridDude, @NumbReg, @fname, @lname, @accnum, @recipients, @EventID, @DateTime
WHILE
@@FETCH_STATUS = 0
BEGIN
exec master..xp_sendmail @recipients, 'Thank you, Please contact us at','','','','','DO NOT REPLY - Your Group Now Qualifies for a Free Couples Registration!',''
INSERT fl_events_grpcrd (GRIDDude, NumbReg, fname, lname, accnum, email, recipients, eventid, [datetime])
values (@GridDude, @NumbReg, @fname, @lname, @accnum, @recipients, @EventID, @DateTime)
FETCH NEXT FROM free_reg INTO @GridDude, @NumbReg, @fname, @lname, @accnum, @recipients, @EventID, @DateTime
END
CLOSE
free_reg
DEALLOCATE
free_reg
N 56°04'39.16"
E 12°55'05.25" -
I am both thankful and humbled. Thank you guys so much.
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply