Subquery returned more than 1 value error

Question

Subquery returned more than 1 value error

rash3554

SSCommitted

Points: 1984
More actions
April 24, 2014 at 3:20 pm

#290582

Hi there,
I was trying to figure out why I get Subquery returned more than 1 value error
create table test1(id int, address varchar(100))
create table finalTbl(id int, address varchar(100))
insert into test1(1,' 2 main st, salem,pa');
insert into test1(2,' 210 keller st, salem,pa');
insert into test1(3,' 2 main st, salem,pa');
insert into test1(4,' 2 main st, acton,pa');
CREATE TYPE [tablevaluedparam] AS TABLE(
[ID] [int] NOT NULL,
[Addr] [varchar](1000) NULL
)

I should look at each @tvp ID and see if there any records in test1 with same address and save all those records to finalTbl at one shot.
DECLARE @rc int
Declare @tvp as [tablevaluedparam];
Begin
insert into @tvp(1,' 2 main st, salem,pa');
insert into @tvp(2,' 210 keller st, salem,pa');
insert into @tvp(4,' 2 main st, acton,pa');
Insert into finalTbl select @tvp.ID, @tvp.address from @tvp left outer join test1 on RTRIM(LTRIM(@tvp.address))=RTRIM(LTRIM(test1.address))
end

I get the correct results.
I Put this Insert into Select statement in a procedure, I get Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.' error.
CREATE PROCEDURE testproc @tvp tablevaluedparam readonly as begin
Insert into finalTbl (ID, address) select @tvp.ID, @tvp.address from @tvp left outer join test1 on RTRIM(LTRIM(@tvp.address))=RTRIM(LTRIM(test1.address))
end
DECLARE @rc int
Declare @tvp as [tablevaluedparam];
Begin
Insert into @tvp(1,' 2 main st, salem,pa');
Insert into @tvp(2,' 210 keller st, salem,pa');
insert into @tvp(4,' 2 main st, acton,pa');
exec @rc=testproc
@tvp
go
I do not understand why this is happening and how to debug this code.
Thanks
Rash

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

Eirikur Eiriksson SSC Guru Points: 182916 More actions · Answer 1

The code you posted has so many syntax errors that I am amazed it managed to run into the error you posted, did a little cleanup and it runs without an error:cool:

USE tempdb;

GO

create table test1(id int, address varchar(100))

create table finalTbl(id int, address varchar(100))

insert into test1(id , address)

VALUES (1,' 2 main st, salem,pa')

,(2,' 210 keller st, salem,pa')

,(3,' 2 main st, salem,pa')

,(4,' 2 main st, acton,pa');

CREATE TYPE [tablevaluedparam] AS TABLE(

[ID] [int] NOT NULL,

[Addr] [varchar](1000) NULL

);

DECLARE @rc int

Declare @tvp [tablevaluedparam];

--Begin

insert into @tvp(id , Addr)

VALUES (1,' 2 main st, salem,pa')

,(2,' 210 keller st, salem,pa')

,(4,' 2 main st, acton,pa');

Insert into finalTbl select t.ID, Addr from @tvp t left outer join test1 on RTRIM(LTRIM(Addr))=RTRIM(LTRIM(test1.address))

--end

CREATE PROCEDURE testproc @tvp [tablevaluedparam] readonly as begin

Insert into finalTbl (ID, address) select t.ID, Addr from @tvp t left outer join test1 on RTRIM(LTRIM(t.Addr))=RTRIM(LTRIM(test1.address))

end

DECLARE @rc int

Declare @tvp [tablevaluedparam];

Insert into @tvp(id , Addr)

VALUES (1,' 2 main st, salem,pa')

,(2,' 210 keller st, salem,pa')

,(4,' 2 main st, acton,pa');

exec @rc=testproc @tvp

Luis Cazares SSC Guru Points: 183706 More actions · Answer 2

Have you checked if you have any triggers on finalTbl (I assume that might not be the real name)?

Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

How to post data/code on a forum to get the best help: Option 1 / Option 2

rash3554 SSCommitted Points: 1984 More actions · Answer 3

rash3554

SSCommitted

Points: 1984

April 24, 2014 at 8:02 pm

#1708651

No, finalTbl does not have any triggers.