Got error like Only one expression can be specified in the select list when the subquery is not introduced with EXISTS

Question

i am exporting some date to sql server using string builder.and here i assign all the values to particular tables.but i don't know i got some error.i can't resolve.help me to resolve this.

ALTER PROCEDURE [dbo].[usp_CreateEmployeDetails]
@nEmployeeDetailsInXML nvarchar(max)=''
As
DECLARE @iTree INTEGER
declare @empid int

BEGIN

SET NOCOUNT ON;
create table #TempRelation(RowNo int identity(1,1),RelationShipId int,RelativeName nvarchar(100),DOB date,IsNominee bit)
create table #Temp_Present_Address(RowNo int identity(1,1),Street1 nvarchar(200),Street2 nvarchar(200),CountryId int,StateId int,CityId int,AddressTypeId int)
create table #Temp_Permanent_Address(RowNo int identity(1,1),Street1 nvarchar(200),Street2 nvarchar(200),CountryId int,StateId int,CityId int,AddressTypeId int)
create table #TempDetails(RowNo int identity(1,1),EmployeeName nvarchar(100),DOB date,DOJ date,Email nvarchar(100),Phone bigint,BoodGroup nchar(10),
PAN_No nvarchar(15),PF_No nvarchar(100),Sex char(10),AccountNo nvarchar(100),BankName nvarchar(100),BranchId int,ManagerId int,HrId int,DesigId int)

exec sp_xml_preparedocument @iTree output,@nEmployeeDetailsInXML

insert into #TempRelation(RelationShipId,RelativeName,DOB,IsNominee)  
select RelationShipId,RelativeName,DOB,IsNominee
from openxml (@iTree,'EmployeeDetails/EmployeeRelation',1)
with(RelationShipId int,RelativeName nvarchar(100),DOB date,IsNominee bit)


insert into #Temp_Permanent_Address(Street1,Street2,CountryId,StateId,CityId,AddressTypeId)  
select Street1,Street2,CountryId,StateId,CityId,AddressTypeId
from openxml (@iTree,'EmployeeDetails/EmployeePermanentAdress',1)
with(Street1 nvarchar(200),Street2 nvarchar(200),CountryId int,StateId int,CityId int,AddressTypeId int)


insert into #Temp_Present_Address(Street1,Street2,CountryId,StateId,CityId,AddressTypeId)
select Street1,Street2,CountryId,StateId,CityId,AddressTypeId
from openxml (@iTree,'EmployeeDetails/EmployeePresentAdress',1)
with(Street1 nvarchar(200),Street2 nvarchar(200),CountryId int,StateId int,CityId int,AddressTypeId int)

insert into #TempDetails(EmployeeName,DOB,DOJ,Email,Phone,BoodGroup,PAN_No,PF_No,Sex,AccountNo,BankName,BranchId,ManagerId,HrId,DesigId)
select EmployeeName,DOB,DOJ,Email,Phone,BoodGroup,PAN_No,PF_No,Sex,AccountNo,BankName,BranchId,ManagerId,HrId,DesigId
from openxml (@iTree,'EmployeeDetails/Employee',1)
with(EmployeeName nvarchar(100),DOB date,DOJ date,Email nvarchar(100),Phone bigint,BoodGroup nchar(10),PAN_No nvarchar(15),PF_No nvarchar(100),Sex char(10),
AccountNo nvarchar(100),BankName nvarchar(100),BranchId int,ManagerId int,HrId int,DesigId int)

if((select COUNT(RowNo) from #TempDetails)>0)
begin
insert into Employee(EmployeeName,DOB,DOJ,Email,Phone,BoodGroup,PAN_No,PF_No,Sex)output inserted.EmployeeId  select EmployeeName,DOB,DOJ,Email,Phone,BoodGroup,PAN_No,PF_No,Sex from #TempDetails
set @empid=SCOPE_IDENTITY()
if((select COUNT(EmployeeName) from Employee where EmployeeId=@empid)>0)
begin
insert into EmployeeAccountDetls(EmployeeId,AccountNo,BankName)values(@empid,(select AccountNo,BankName from #TempDetails))
insert into EmployeeLink(EmployeeId,BranchId,ManagerId,HrId,DesigId)values(@empid,(select BranchId,ManagerId,HrId,DesigId from #TempDetails)) 
if((select COUNT(RowNo) from #Temp_Permanent_Address)>0)
begin
insert into EmployeeAddress(EmployeeId,Street1,Street2,CountryId,StateId,CityId,AddressTypeId)values(@empid,(select Street1,Street2,CountryId,StateId,CityId,AddressTypeId from #Temp_Permanent_Address))
end
if((select COUNT(RowNo) from #Temp_Present_Address)>0)
begin
insert into EmployeeAddress(EmployeeId,Street1,Street2,CountryId,StateId,CityId,AddressTypeId)values(@empid,(select Street1,Street2,CountryId,StateId,CityId,AddressTypeId from #Temp_Present_Address))
end
if((select COUNT(RowNo) from #TempRelation)>0)
begin
insert into EmployeeRelationDetls(EmployeeId,RelationShipId,RelativeName,DOB,isNominee)values(@empid,(select RelationShipId,RelativeName,DOB,IsNominee from #TempRelation))
end
end
end

 EXEC sp_xml_removedocument @iTree

 drop table #Temp_Present_Address
 drop table #Temp_Permanent_Address
 drop table #TempDetails
 drop table #TempRelation
 END

why its happening i checked but i didn't get the result

Answer 1

For the specific error you mention, your problem is here:

insert into EmployeeAddress(EmployeeId,Street1,Street2,CountryId,StateId,CityId,AddressTypeId)
values (@empid, (select Street1,Street2,CountryId,StateId,CityId,AddressTypeId from #Temp_Permanent_Address))

You probably want:

insert into EmployeeAddress(EmployeeId,Street1,Street2,CountryId,StateId,CityId,AddressTypeId)
SELECT @empid,Street1,Street2,CountryId,StateId,CityId,AddressTypeId from #Temp_Permanent_Address

However, you're inserting multiple rows and then giving the last rows IDENTITY value to every address row doing that. You need to find a better way of relating keys - which can either be done using MERGE's output clause or by using a stronger key to reference back to the table you just inserted into in order to find the newly inserted employee ID.