April 24, 2014 at 3:20 pm
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
April 24, 2014 at 4:01 pm
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
April 24, 2014 at 4:16 pm
Have you checked if you have any triggers on finalTbl (I assume that might not be the real name)?
April 24, 2014 at 8:02 pm
No, finalTbl does not have any triggers.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply