August 12, 2011 at 10:26 am
I have a problem with my stored procedure when I change the value of @montant from 1000 to 9000 its runs fine but whenever I put a value of 10000 and above its display some thing lik this:<<
(1 row(s) affected)
(1 row(s) affected)
Msg 512, Level 16, State 1, Procedure transfertcash, Line 63
Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.
The statement has been terminated.
Msg 8114, Level 16, State 5, Procedure transfertcash, Line 65
Error converting data type varchar to float.>>
Pliz see My tables and stored procedure ,then tell me what is wrong...
tables:
create table MyLogin1
(
id int identity not null primary key nonclustered,
numtel nvarchar(50)constraint fkidc foreign key references dbo.McellAcc(numtel),
nom nvarchar(50)not null,
prenom nvarchar(50) not null,
idsession nvarchar(50)not null
);
create table TransactionM
(
idtrans int identity not null primary key nonclustered,
numtel nvarchar(50)constraint fknu foreign key references dbo.McellAcc(numtel),
debitmonta nvarchar(50) null,
creditmonta nvarchar(50) null,
datetrans datetime not null,
taux float,
etat nvarchar(50)null
);
create table McellAcc
(
numtel nvarchar(50)constraint pknumt primary key nonclustered,
idcarte nvarchar(50)constraint fkidca foreign key references dbo.Client(idcarte),
imsi nvarchar(50) unique not null,
dateouv datetime not null,
solde nvarchar(50) null,
etat nvarchar(50)not null,
motif nvarchar(50) not null
);
create table ozekimessageout
(
id int identity(1,1),
sender nvarchar(50),
receiver nvarchar(50),
msg nvarchar(50),
senttime nvarchar(50),
receivedtime nvarchar(50),
operator nvarchar(50),
msgtype nvarchar(50),
reference nvarchar(50),
statu nvarchar(50),
errormsg nvarchar(50)
);
stored procedure:
ALTER PROC [dbo].[transfertcash]
(
@numb nvarchar(50),
@montant float,
@numtel bigint,
@code nvarchar(50),
@msg_to_send as nvarchar(4000) output
--@SoldeActuMob as float=null output
)
AS
begin --1
declare @num bigint
declare @balance as float
declare @statusC varchar(50)
declare @imsi varchar(50)
declare @diff as float
declare @nom varchar(50)
declare @prenom varchar(50)
declare @idtrans as int
declare @etat varchar(50)
declare @taux as float
set @nom=(select nom from dbo.mylogin1 where numtel=@numtel)
set @prenom=(select prenom from dbo.mylogin1 where numtel=@numtel)
set @statusC = (select etat from McellAcc where numtel = @numtel)
if @statusC = 'opened'
begin --2
set @num = (select numtel from McellAcc where numtel =@numtel)
if @num > 0 --numtel found
begin --3
set @balance =( select solde from McellAcc where numtel =@numtel)
set @diff = @balance - @montant
if @diff < 0
begin --4--compare amounts
set @msg_to_send = 'Insufficiant balance, impossible to transfer e-cash!'
end --4
else --if diff>0
begin --5
if @montant > = '10000' and @montant < = '100000'
begin --6
insert into TransactionM( --retrait cpte tigo
numtel,
debitmonta,
creditmonta,
DateTrans,
taux,
etat
)
values(
@numtel,
@montant,
0,
getdate(),
@taux,
@etat
)
UPDATE McellAcc set solde = solde - @montant where numtel=@numtel
UPDATE TransactionM set etat= 'Pending...' where idtrans=(SELECT idtrans from TransactionM where numtel=@numtel)
set @msg_to_send = 'You received '+ @montant + ' from ' + @nom+','+@prenom+','+@num +','+@idtrans;
insert into ozekimessageout(receiver,msg,status) values (@numb,@msg_to_send,'send')
end--6
else
begin --6
set @msg_to_send = 'You are sending less or more than allowed!'
end --6
end --5
end
end
else
begin --2--status fermé
set @msg_to_send='Transaction failed,try again!'
end --1
end
August 12, 2011 at 10:35 am
It would appear to me that prior to using 10000, you have been lucky that there are no duplicate values.
set @nom=(select nom from dbo.mylogin1 where numtel=@numtel)
set @prenom=(select prenom from dbo.mylogin1 where numtel=@numtel)
set @statusC = (select etat from McellAcc where numtel = @numtel)
In all likelihood you are getting multiple values from one of these (or another variable set operation like them) that is causing your problem. Check your data and see what you get.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
August 15, 2011 at 4:11 am
You can replace your:
set @nom=(select nom from dbo.mylogin1 where numtel=@numtel)
With
select @nom = nom from dbo.mylogin1 where numtel=@numtel
It will never fail again, however it will hide the fact that your query would select multiple values for the same condition. In the above case, if you have two different "nom" for the same "numtel" (eg. 10000) you will never can be sure which one will be selected.
Viewing 3 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply