出现错误时如果未使用EXISTS引入子查询，则只能在选择列表中指定一个表达式

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.

我正在使用字符串builder来向sql server输出一些日期。在这里我将所有值分配给特定的表。但我不知道我有一些错误。我无法解决。帮助我解决这个问题。

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

为什么它发生了我检查但我没有得到结果

1 个解决方案

#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.

但是,您要插入多行,然后将最后一行IDENTITY值提供给每个执行此操作的地址行。您需要找到一种更好的关联密钥的方法 - 可以使用MERGE的输出子句或使用更强的密钥引用回您刚刚插入的表来查找新插入的员工ID。