Convertir campo

[chicorio]

Como convierto un campo q es recibido en un strored procedure como varchar a integer

[huesos52]

Lo puedes hacer con la función cast.

select cast(var_varchar as unsigned)

Un saludo.

[chicorio]

Y para asignarle esta conversion a una variable ????

[huesos52]

Asumo que es igual a como te dije en un post pasado.

Código sp:

declare entero integer;
select cast(variable_char as unsigned) into entero;
 

Un saludo.

[chicorio]

me da error de sintaxis........

[huesos52]

Que error te muestra? como lo haces?
Cual es el store procedure?

La función cast lo que hace es convertir a otro tipo de dato.
select cast('2' as unsigned) te pasa el '2' varchar a 2 entero.

trata de ser un poco mas explicito chicorio.

[chicorio]

CREATE DEFINER=`teletaxusr`@`%` PROCEDURE `Costs_Calls`(begindate nvarchar(10), enddate nvarchar (10), firstExt varchar (15),LastExt varchar (15), CallType int )
BEGIN


Declare starDatecall datetime;
Declare EndDateCall datetime;
Declare BeginExt integer;

select cast(concat(begindate ,' 00:00:00') AS datetime) into starDatecall;
select cast(concat(enddate ,' 23:59:59') AS datetime) into EndDateCall ;
select cast(firstExt as unsigned) into BeginExt;

Ahi despues sigue el resto del sp el error solo dice error de sintaxis en la linea 16

[chicorio]

CREATE DEFINER=`teletaxusr`@`%` PROCEDURE `Costs_Calls`(begindate nvarchar(10), enddate nvarchar (10), firstExt varchar (15), LastExt varchar (15), CallType int )
BEGIN


/* Creted by Calos Murillo April 2009

calaculate cost of customers calls */


Declare starDatecall datetime;
Declare EndDateCall datetime;
Declare BeginExt integer;

select cast(concat(begindate ,' 00:00:00') AS datetime) into starDatecall;
select cast(concat(enddate ,' 23:59:59') AS datetime) into EndDateCall ;
select cast(firstExt as unsigned) into BeginExt;

DROP TABLE IF EXISTS Calc;
create temporary table Calc
(Call_Date datetime, cost float (10,3), extension varchar (15), number varchar (10), Call_type varchar (15));


if CallType = 0 then

insert into Calc (Call_Date, cost, extension,number, Call_type )
select ctime,( CEIL(duration /60 ) *
Case when LEFT(calling_num , 1) = '2' and CHARACTER_LENGTH(calling_num )= 8
then 0.01
WHEN LEFT(calling_num , 1) = '8' and CHARACTER_LENGTH(calling_num )= 8
then 0.10
WHEN CHARACTER_LENGTH(calling_num )>= 10
then 0.16
end ) cost, dialed_num, calling_num , ('in')
from tbCalls
where ctime >= starDatecall
and ctime <= EndDateCall
and dialed_num between firstExt and lastExt
and duration > 0
and calling_num <> '';

insert into Calc (Call_Date, cost, extension,number, Call_type )
select ctime,( CEIL(duration /60 ) *
Case When LEFT(dialed_num, 1) = '2' and CHARACTER_LENGTH(dialed_num )= 8
then 0.05
When LEFT(dialed_num, 1) = '8' and CHARACTER_LENGTH(dialed_num)= 8
then 0.10
When LEFT(dialed_num, 1) = '1' and CHARACTER_LENGTH(dialed_num)=12
then 0.16
When LEFT(dialed_num, 2) = '00'
then 0.50
When CHARACTER_LENGTH(dialed_num)=10
then 0.16
else
0.05
End)cost, calling_num, dialed_num, ('out')
from tbCalls
where ctime >= starDatecall
and ctime <= EndDateCall
and calling_num between firstExt and lastExt
and duration > 0;

end if;


if CallType = 1 then

insert into Calc (Call_Date, cost, extension,number, Call_type )
select ctime,( CEIL(duration /60 ) *
Case when LEFT(calling_num , 1) = '2' and CHARACTER_LENGTH(calling_num )= 8
then 0.01
WHEN LEFT(calling_num , 1) = '8' and CHARACTER_LENGTH(calling_num )= 8
then 0.10
WHEN CHARACTER_LENGTH(calling_num )>= 10
then 0.16
end ) cost, dialed_num, calling_num , ('in')
from tbCalls
where ctime >= starDatecall
and ctime <= EndDateCall
and dialed_num between firstExt and lastExt
and duration > 0
and CHARACTER_LENGTH(calling_num )> 4;
end if;






if CallType = 2 then



insert into Calc (Call_Date, cost, extension,number, Call_type )
select ctime,( CEIL(duration /60 ) *
Case When LEFT(dialed_num, 1) = '2' and CHARACTER_LENGTH(dialed_num )= 8
then 0.05
When LEFT(dialed_num, 1) = '8' and CHARACTER_LENGTH(dialed_num)= 8
then 0.10
When LEFT(dialed_num, 1) = '1' and CHARACTER_LENGTH(dialed_num)=12
then 0.16
When LEFT(dialed_num, 2) = '00'
then 0.50
When CHARACTER_LENGTH(dialed_num)=10
then 0.16
else
0.05
End)cost, calling_num, dialed_num, ('out')
from tbCalls
where ctime >= starDatecall
and ctime <= EndDateCall
and dialed_num > 4
and calling_num >=firstExt
and calling_num <=lastExt
and duration > 0;

end if;



select * from Calc
order by extension,Call_Date;


END

[huesos52]

Donde te señala el error de sintaxis?
No parece ser en la conversión a integer.

[chicorio]

mmm solo cuando agrego esa linea me da el error si la quito me permite guardar los cambios

[chicorio]

Listo funciono gracias

[huesos52]

cual era el error?

[chicorio]

El erro estaba en la transformacion cuando hacia el cast

select cast(firstExt as unsigned) into BeginExt; por error en el codigo q tenia en mi pc escribi integer en lugar de unsigned