dimanche 19 novembre 2017

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

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