i try to execute below code in my store procedure and i use insert into select statement for insert some data to new table that I want but i get error .
ALTER procedure [dbo].[USP_AddTerminalInfo1]
@list terminalinfo2 READONLY,
@result int output
as
begin
declare @Check int
declare @CustomerNo nvarchar(20)
declare @serialNumber nvarchar(20)
declare @POSID int
select * into #temp from @list
while( select * from #temp) >0
begin
select top 1 @Check=terminalNo from #temp
--process
select @CustomerNo=customerNo from @list where terminalNo=@Check
select @serialNumber=serialNumber from @list where terminalNo=@Check
if exists (select customerNo from TBL_Customer where customerNo=@CustomerNo)
begin
if exists ( select serialNumber from TBL_POS where serialNumber=@serialNumber)
begin
select @POSID=ID from TBL_POS where serialNumber=@serialNumber;
insert into TBL_Terminals (terminalNo,PlaceName,placeAddress,placePhone,mobile,placePostalCode,bankID,bankBranchID,pspBankID,accountNumber,sheba,guildID,takhsisDate,DutyCycleID,StatusID,topStatusID,City,firstTransactionDate,ContractNo,terminalType) select terminalNo,PlaceName,placeAddress,placePhone,mobile,placePostalCode,bankID,bankBranchID,pspBankID,accountNumber,sheba,guildID,takhsisDate,DutyCycleID,StatusID,topStatusID,City,firstTransactionDate,ContractNo,terminalType from @list;
update TBL_Terminals set CustomerNo = @CustomerNo ,POSID=@POSID where terminalNo=@Check;
end
else
begin
insert into TBL_POS (serialNumber,PosStatus) select serialNumber,PosStatus from @list;
select @POSID=ID from TBL_POS where serialNumber=@serialNumber;
insert into TBL_Terminals (terminalNo,PlaceName,placeAddress,placePhone,mobile,placePostalCode,bankID,bankBranchID,pspBankID,accountNumber,sheba,guildID,takhsisDate,DutyCycleID,StatusID,topStatusID,City,firstTransactionDate,ContractNo,terminalType) select terminalNo,PlaceName,placeAddress,placePhone,mobile,placePostalCode,bankID,bankBranchID,pspBankID,accountNumber,sheba,guildID,takhsisDate,DutyCycleID,StatusID,topStatusID,City,firstTransactionDate,ContractNo,terminalType from @list;
update TBL_Terminals set CustomerNo = @CustomerNo ,POSID=@POSID where terminalNo=@Check;
end
end
else
begin
insert into TBL_Customer (customerNo,fullName,nationalCode,houseAddress,housePhone,createDate) select customerNo,fullName,nationalCode,houseAddress,housePhone,createDate from @list;
select @CustomerNo=customerNo from TBL_Customer where customerNo=@CustomerNo;
if exists ( select count(serialNumber) from TBL_POS where serialNumber=@serialNumber)
begin
select @POSID=ID from TBL_POS where serialNumber=@serialNumber;
insert into TBL_Terminals (terminalNo,PlaceName,placeAddress,placePhone,mobile,placePostalCode,bankID,bankBranchID,pspBankID,accountNumber,sheba,guildID,takhsisDate,DutyCycleID,StatusID,topStatusID,City,firstTransactionDate,ContractNo,terminalType) select terminalNo,PlaceName,placeAddress,placePhone,mobile,placePostalCode,bankID,bankBranchID,pspBankID,accountNumber,sheba,guildID,takhsisDate,DutyCycleID,StatusID,topStatusID,City,firstTransactionDate,ContractNo,terminalType from @list;
update TBL_Terminals set CustomerNo = @CustomerNo ,POSID=@POSID where terminalNo=@Check;
end
else
begin
insert into TBL_POS (serialNumber,PosStatus) select serialNumber,PosStatus from @list;
select @POSID=ID from TBL_POS where serialNumber=@serialNumber;
insert into TBL_Terminals (terminalNo,PlaceName,placeAddress,placePhone,mobile,placePostalCode,bankID,bankBranchID,pspBankID,accountNumber,sheba,guildID,takhsisDate,DutyCycleID,StatusID,topStatusID,City,firstTransactionDate,ContractNo,terminalType) select terminalNo,PlaceName,placeAddress,placePhone,mobile,placePostalCode,bankID,bankBranchID,pspBankID,accountNumber,sheba,guildID,takhsisDate,DutyCycleID,StatusID,topStatusID,City,firstTransactionDate,ContractNo,terminalType from @list;
update TBL_Terminals set CustomerNo = @CustomerNo ,POSID=@POSID where terminalNo=@Check;
end
end
--process
delete #temp where terminalNo=@Check;
end
end
But code throw this error :
Only one expression can be specified in the select list when the subquery is not introduced with EXISTS.
what should I do?
Aucun commentaire:
Enregistrer un commentaire