September 8, 2009 at 5:37 pm
Hi, I don't know if this topic is posted in another forum, but I need help, I can't find anything about it.
I need to create a function, and inside that fuction i need a Cursor is this possible?
here is my code
ALTER FUNCTION fn_ImporteconDescuento (@Importe float(8),@Cliente varchar(10))
RETURNS float (20)
AS
BEGIN
Declare @Porcentaje Float(8)
,@SQL varchar(5000)
,@AuxImporte Float(20)
,@Desc Float(8)
Declare Cur_Importe Cursor Read_Only For
Select op.porcentaje from Openquery(ek_adm99, 'select dp.porcentaje,cp.numcte from sm_cliente_planpago cp inner join
sm_descuentos_planpago dp on dp.id_num_cptopago=cp.id_num_cptopago order by dp.Orden')op where op.numcte=@Cliente
Open Cur_importe
fetch next from Cur_Importe into @porcentaje
Set @AuxImporte = @Importe
While @@Fetch_status>0
Begin
Set @AuxImporte=0
Set @Desc= @AuxImporte * @Porcentaje /100
Set @AuxImporte = @AuxImporte - @Desc
fetch next from cur_importe into @porcentaje
End
close cur_importe
Deallocate cur_importe
Set @Importe= @AuxImporte
RETURN(@AuxImporte)
END
September 8, 2009 at 7:02 pm
I found the solution just in case someone whant's to know i just change the @@Fetch_Status for @@Cursor_Rows and added a counter
ALTER FUNCTION fn_ImporteconDescuento (@Importe float(8),@Cliente varchar(10))
RETURNS Float(8)
AS
BEGIN
Declare @Porcentaje varchar(10)
,@Currentdesc int
,@AuxImporte Float(20)
,@Desc Float(8)
,@Counter int
Set @CurrentDesc = 0
Declare Cur_Importe Cursor Local Scroll For
Select op.porcentaje from Openquery(ek_adm99, 'select dp.porcentaje,cp.numcte from sm_cliente_planpago cp inner join
sm_descuentos_planpago dp on dp.id_num_cptopago=cp.id_num_cptopago order by dp.Orden')op where op.numcte=@Cliente
Open Cur_importe
fetch next from Cur_Importe into @porcentaje
Set @AuxImporte = @Importe
While @CurrentDesc<@@CURSOR_ROWS
Begin
Set @Desc= @AuxImporte * @Porcentaje /100
Set @AuxImporte = @AuxImporte - @Desc
fetch next from cur_importe into @porcentaje
Set @CurrentDesc=@CurrentDesc + 1
End
close cur_importe
Deallocate cur_importe
Set @Importe=@AuxImporte
RETURN(@Importe)
END
Thanks
September 9, 2009 at 12:17 am
Oh my, no....
You don't need the slothfulness nor resource usage of a cursor here. I don't have access to an SQL Server this week but consider the following code...
DECLARE @Result INT
SET @Result = 0
SELECT @Result = @Result + t.Number
FROM Master.dbo.spt_Values t
WHERE t.Type = 'P'
AND t.Number <= 4
SELECT @Result
To see the magic that you could apply to your code, figure out why the value of @Result will be returned as the number 10.
Once you figure that out, use the knowledge to get rid of the cursor... it's bad enough that you're using a UDF.
--Jeff Moden
Change is inevitable... Change for the better is not.
September 9, 2009 at 11:37 am
Thanks Jeff for the reply, I try your idea but I'm staying with the cursor is almost 30 seconds faster.
September 9, 2009 at 9:40 pm
Then you've done something wrong with the idea. Post the code and let's have a peek.
--Jeff Moden
Change is inevitable... Change for the better is not.
September 10, 2009 at 12:54 am
Not having anything with which to test it, I think this is what Jeff was thinking:
ALTER FUNCTION fn_ImporteconDescuento (@Importe float(8),@Cliente varchar(10))
RETURNS Float(8)
AS
BEGIN
Declare @AuxImporte Float(20);
set @AuxImporte = @Importe
Select
@AuxImporte = @AuxImporte * op.porcentaje / 100
from Openquery(ek_adm99, 'select
dp.porcentaje,
cp.numcte
from
sm_cliente_planpago cp
inner join sm_descuentos_planpago dp
on (dp.id_num_cptopago = cp.id_num_cptopago)
order by
dp.Orden') op
where
op.numcte = @Cliente;
RETURN(@AuxImporte);
END
Give this a try.
September 10, 2009 at 12:59 pm
I actually modified the function because I had to include extra info this is the final version of the function
ALTER FUNCTION fn_ImporteconDescuentoFinal (@Importe float(8),@Cliente varchar(10))
RETURNS Float(8)
AS
BEGIN
Declare @Porcentaje Float(8)
,@Currentdesc int
,@AuxImporte Float(20)
,@Desc Float(8)
Set @AuxImporte = @Importe
if exists(Select * from Openquery(ek_adm99, 'select * from sm_clientes_descuentos cd
inner join sm_descuentos_planpago cp on cp.id_num_descuento=cd.id_num_descuento')op where op.numcte=@Cliente)
Begin
--Calculate spetial discounts from sm_Clientes_Descuentos
Select @AuxImporte = @AuxImporte - (@AuxImporte * op1.porc_desc / 100) from Openquery(ek_adm99, 'select cd.porc_desc,cd.numcte from sm_clientes_descuentos cd
inner join sm_descuentos_planpago dp on dp.id_num_descuento=cd.id_num_descuento order by dp.Orden')op1 where op1.numcte=@Cliente
--Calculate normal discounts from Sm_Descuentos_PlanPago
Select @AuxImporte = @AuxImporte - (@AuxImporte * op.porcentaje / 100) from Openquery(ek_adm99, 'select dp.porcentaje,cp.numcte,dp.id_num_descuento from sm_cliente_planpago cp inner join
sm_descuentos_planpago dp on dp.id_num_cptopago=cp.id_num_cptopago order by dp.Orden')op where op.numcte=@Cliente and
op.id_num_descuento not in (Select op1.id_num_descuento from Openquery(ek_adm99, 'select cd.id_num_descuento,cd.numcte from sm_clientes_descuentos cd
inner join sm_descuentos_planpago dp on dp.id_num_descuento=cd.id_num_descuento order by dp.Orden')op1 where op1.numcte=@Cliente)
End
Else
Begin
--Calculate discounts if there is no spetials
Select @AuxImporte = @AuxImporte - (@AuxImporte * op.porcentaje / 100) from Openquery(ek_adm99, 'select dp.porcentaje,cp.numcte from sm_cliente_planpago cp inner join
sm_descuentos_planpago dp on dp.id_num_cptopago=cp.id_num_cptopago order by dp.Orden')op where op.numcte=@Cliente
End
Set @Importe=@AuxImporte
RETURN(@Importe)
END
September 10, 2009 at 3:34 pm
vallolet04 (9/10/2009)
I actually modified the function because I had to include extra info this is the final version of the functionALTER FUNCTION fn_ImporteconDescuentoFinal (@Importe float(8),@Cliente varchar(10))
RETURNS Float(8)
AS
BEGIN
Declare @Porcentaje Float(8)
,@Currentdesc int
,@AuxImporte Float(20)
,@Desc Float(8)
Set @AuxImporte = @Importe
if exists(Select * from Openquery(ek_adm99, 'select * from sm_clientes_descuentos cd
inner join sm_descuentos_planpago cp on cp.id_num_descuento=cd.id_num_descuento')op where op.numcte=@Cliente)
Begin
--Calculate spetial discounts from sm_Clientes_Descuentos
Select @AuxImporte = @AuxImporte - (@AuxImporte * op1.porc_desc / 100) from Openquery(ek_adm99, 'select cd.porc_desc,cd.numcte from sm_clientes_descuentos cd
inner join sm_descuentos_planpago dp on dp.id_num_descuento=cd.id_num_descuento order by dp.Orden')op1 where op1.numcte=@Cliente
--Calculate normal discounts from Sm_Descuentos_PlanPago
Select @AuxImporte = @AuxImporte - (@AuxImporte * op.porcentaje / 100) from Openquery(ek_adm99, 'select dp.porcentaje,cp.numcte,dp.id_num_descuento from sm_cliente_planpago cp inner join
sm_descuentos_planpago dp on dp.id_num_cptopago=cp.id_num_cptopago order by dp.Orden')op where op.numcte=@Cliente and
op.id_num_descuento not in (Select op1.id_num_descuento from Openquery(ek_adm99, 'select cd.id_num_descuento,cd.numcte from sm_clientes_descuentos cd
inner join sm_descuentos_planpago dp on dp.id_num_descuento=cd.id_num_descuento order by dp.Orden')op1 where op1.numcte=@Cliente)
End
Else
Begin
--Calculate discounts if there is no spetials
Select @AuxImporte = @AuxImporte - (@AuxImporte * op.porcentaje / 100) from Openquery(ek_adm99, 'select dp.porcentaje,cp.numcte from sm_cliente_planpago cp inner join
sm_descuentos_planpago dp on dp.id_num_cptopago=cp.id_num_cptopago order by dp.Orden')op where op.numcte=@Cliente
End
Set @Importe=@AuxImporte
RETURN(@Importe)
END
How does this compare to your cursor-based solution?
Also, you could just do this at the end and eliminate the last set:
RETURN(@AuxImporte)
September 10, 2009 at 3:58 pm
Hi Lynn I just realized that thanks and is working fine I didn't try the cursor idea at the end, because it was taking like a minute and a half to return data from 1 moth and I'm going to working with a larger amount of records I didn't want to waste more time testing.
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply