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 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy