August 31, 2016 at 12:28 pm
Hi All.
First sorry for my bad english
I have a problem with a do while loop in stored procedure with a cursor
Never executing the last "fetch into", I believe that the problem is in the Begin End structure but i am very novice with T-SQL Stored Procedures.
here is the code:
-- =============================================
-- Author:<Antonio Pérez Fuerte>
-- Create date: <23/08/2016>
-- Description:<Generación de información para Control CLientes DMA>
-- =============================================
CREATE PROCEDURE [dbo].[ControlClientesHFT]
AS
SET NOCOUNT ON
BEGIN
DECLARE @NOMBRE NVARCHAR(40)
DECLARE @LISTASINONIMOS NVARCHAR(MAX)
DECLARE @EFEORDLIM NUMERIC(18)
DECLARE @EFEDIRLIM NUMERIC(18)
DECLARE @MAXORDDIA NUMERIC(18)
DECLARE @MAXORDSEG NUMERIC(18)
DECLARE @EFEORDMAX NUMERIC(18)
DECLARE @EFEDIRREA NUMERIC(18)
DECLARE @ORDDIRREA NUMERIC(18)
DECLARE @ORDSEGREA INTEGER
DECLARE @HFT BIT
DECLARE @EFEORDCTL NVARCHAR(3)
DECLARE @EFEDIRCTL NVARCHAR(3)
DECLARE @ORDDIACTL NVARCHAR(3)
DECLARE @ORDSEGCTL NVARCHAR(3)
declare @fechasesion nvarchar(8)--SET @fecha =convert(nvarchar(8),@fechasesion,112)
DECLARE @cFechaMenos1 varchar(8)
DECLARE @cFecha1Enero varchar(8)
--calculamos D-2
SELECT @cFechaMenos1=convert(varchar(8),DATEADD(day, -2, getdate()), 112)
If datepart(dw,@cFechaMenos1) = 1--DOMINGO
SELECT @cFechaMenos1 = convert(varchar(8),dateadd(day,-3, @cFechaMenos1), 112)
IF datepart(dw,@cFechaMenos1) = 7--SÁBADO
SELECT @cFechaMenos1 = convert(varchar(8),dateadd(day,-2, @cFechaMenos1),112)
select @fechasesion = @cFechaMenos1
DECLARE cur_cli_dma CURSOR FOR
SELECT DM_Clientes.Nombre, DM_CLISIN.CODSIN, DM_Clientes.LimitByOrderValue, DM_Clientes.LimitByEntityValue, DM_Clientes.MAXORDDIA, DM_Clientes.MAXORDSEG, DM_Clientes.HFT
FROM DM_Clientes INNER JOIN
DM_CLISIN ON DM_Clientes.IdCliente = DM_CLISIN.IdCliente
WHERE (DM_Clientes.DMA = 1) AND (DM_CLISIN.TIPSIN = N'35') ORDER BY DM_Clientes.Nombre
OPEN cur_cli_dma
FETCH cur_cli_dma INTO @NOMBRE,@LISTASINONIMOS,@EFEORDLIM,@EFEDIRLIM,@MAXORDDIA,@MAXORDSEG,@HFT
WHILE @@fetch_status >= 0
BEGIN
--Calculamos Efectivo Maximo por orden Introducida
SELECT @EFEORDMAX = DBO.fc_MaxEfeCli (@Fechasesion,@LISTASINONIMOS)
IF @EFEORDMAX IS NULL
BEGIN
SELECT @EFEORDMAX=0
SELECT @EFEORDCTL='OK'
END
ELSE
BEGIN
IF @EFEORDMAX > @EFEORDLIM
BEGIN
SELECT @EFEORDCTL='KO'
END
ELSE
SELECT @EFEORDCTL='OK'
END
END;
--CALCULAMOS EFECTIVO CONTRATADO EN EL DIA
SELECT @EFEDIRREA = DBO.FC_EFECLIREA(@Fechasesion,@LISTASINONIMOS)
IF @EFEDIRREA IS NULL
BEGIN
SELECT @EFEDIRREA=0
SELECT @EFEDIRCTL='OK'
END
ELSE
BEGIN
IF @EFEORDMAX > @EFEORDLIM
BEGIN
SELECT @EFEDIRCTL ='KO'
END
ELSE
SELECT @EFEDIRCTL='OK'
END
END;
--Calculamos ordenes por segundo chequeamos con limites
SELECT @ORDSEGREA = dbo.FC_NumMaxOrdSeg (@Fechasesion,@LISTASINONIMOS)
IF @ORDSEGREA IS NULL
BEGIN
SELECT @ORDSEGREA=0
SELECT @ORDSEGCTL ='OK'
END
ELSE
BEGIN
IF @ORDSEGREA > @MAXORDSEG
BEGIN
SELECT @ORDSEGCTL ='KO'
END
ELSE
BEGIN
SELECT @ORDSEGCTL ='OK'
END
END;
IF @HFT = 1
BEGIN
SELECT @ORDSEGCTL ='HFT'
END;
--Calculamos ordenes DIARIAS chequeamos con limites
SELECT @ORDDIRREA = dbo.FC_NumOrdCli (@Fechasesion,@LISTASINONIMOS)
IF @ORDDIRREA IS NULL
BEGIN
SELECT @ORDDIRREA=0
SELECT @ORDDIACTL ='OK'
END
ELSE
BEGIN
IF @ORDDIRREA > @MAXORDDIA
BEGIN
SELECT @ORDDIACTL ='KO'
END
ELSE
BEGIN
SELECT @ORDDIACTL ='OK'
END
END;
IF @HFT =1
BEGIN
SELECT @ORDDIACTL ='HFT'
END
ELSE
--INSERTAMOS EN UC_CONTROL_CLIENTES_HFT
EXEC sp_UC_CONTROL_CLIENTES_HFT_INSERT
@fechasesion,
@NOMBRE,
@EFEORDMAX,
@EFEORDLIM,
@EFEORDCTL ,
@EFEDIRREA ,
@EFEDIRLIM ,
@EFEDIRCTL ,
@ORDDIRREA ,
@MAXORDDIA ,
@ORDDIACTL ,
@ORDSEGREA ,
@MAXORDSEG ,
@ORDSEGCTL
--PRINT @NOMBRE + '|' + cast(@ORDSEGREA as nvarchar(15)) + '|' + cast(@ORDDIRREA as nvarchar(15)) + '|' + cast(@EFEORDMAX as nvarchar(15)) + '|' + cast(@EFEDIRREA as nvarchar(15))
FETCH cur_cli_dma INTO @NOMBRE,@LISTASINONIMOS,@EFEORDLIM,@EFEDIRLIM,@MAXORDDIA,@MAXORDSEG,@HFT
CLOSE cur_cli_dma
DEALLOCATE cur_cli_dma
Thanks in Advance
August 31, 2016 at 12:35 pm
What exactly do you mean?
The only loop I can find doesn't have a fetch next so yes it's going to be broken.
And please indent your code, my poor poor eyes.
August 31, 2016 at 12:37 pm
Thanks, this is the error , i´m blind "fetch next" .......;-)
August 31, 2016 at 12:45 pm
There are a number of things I would recommend from this code. First, try to figure out what all those functions are doing because using a loop like this is killing performance for you. I notice you also have some of your procedures using the sp_ prefix. My recommendation is to not use any prefix as they are really just noise but if you must, you should consider a different prefix. sp_ does NOT mean stored procedure. It means system procedure and using it yourself can cause a number of challenges. http://sqlperformance.com/2012/10/t-sql-queries/sp_prefix
I suspect once you understand those functions you could probably rewrite this whole thing into a set based process.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
August 31, 2016 at 3:43 pm
Thanks Sean
I rewrote the stored procedure eliminating the sp_ prefix in the call to another procedure, regarding the function calls do not quite understand you want to tell me, is better than all code functions (actually functions only execute a query and return one result) write it in the process?
On the other hand (and this was the reason for my first post), the structure of the procedure okay ?, I think so much begin end makes the code may not run properly.
September 1, 2016 at 7:35 am
antonio.perez (8/31/2016)
Thanks SeanI rewrote the stored procedure eliminating the sp_ prefix in the call to another procedure, regarding the function calls do not quite understand you want to tell me, is better than all code functions (actually functions only execute a query and return one result) write it in the process?
On the other hand (and this was the reason for my first post), the structure of the procedure okay ?, I think so much begin end makes the code may not run properly.
All those functions are scalar functions which can cause some serious performance problems. Then you have those inside a cursor. You have a recipe for really bad performance. The idea would be to get rid of the loop but to do that requires an understanding of all those functions. And the work involved is quite likely going to be more than is reasonable for an online forum.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply