August 5, 2018 at 3:43 am
Dear Friends,
I am new to TSQL programming . Need your help in making this SP work. The first test case I am using is 'What happens when the Cursor query returns no records'. Though there is not record returned when by this query ,the SP keeps running without doing anything instead of closing the cursor and exiting. Please advise where the problem is. I've copy pasted the code for the SP below .Thank you in advance.
Though there are no records , the control goes in While loop which keeps running printing the inner PRINT after running the PRINT just before the WHILE once. Please advise why behaves this way
USE [JDE_DEVELOPMENT]
GO/****** Object: StoredProcedure [dbo].[ImportInvoices_Validations_CrossQueryCursor] Script Date: 05-Aug-18 12:27:13 PM ******/
SET ANSI_NULLS ON
GOSET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[ImportInvoices_ZeroRecords]
WITH EXECUTE AS OWNER
AS/****** Variable Declarations for [F03B11Z1] ******/
DECLARE
@ErrorCode NVARCHAR(40);/********* Cursor definition with outer join of [F03B11Z1] WITH [F0911Z1] ********/
DECLARE Invoices1 CURSOR FOR SELECT * FROM [JDEORIONDV].[PRODDTA].[F03B11Z1] I LEFT OUTER JOIN [JDEORIONDV].[PRODDTA].[F0911Z1] J ON I.VJEDUS = J.VNEDUS AND I.VJEDTN = J.VNEDTN AND I.VJEDLN = J.VNEDLN AND I.VJEDSP = J.VNEDSP AND I.VJEDBT = J.VNEDBT AND I.VJDOC = J.VNDOC AND I.VJDCT = J.VNDCT AND I.VJKCO = J.VNKCO AND I.VJSFX = J.VNSFX AND I.VJEDSP = 0 WHERE I.VJEDSP = 0; BEGIN
OPEN Invoices1
--LOOP UNTIL RECORDS ARE AVAILABLE.
PRINT 'I am just before while';
WHILE @@FETCH_STATUS = 0
PRINT 'I am in While';
SET @errorcode = '';
CLOSE Invoices1;
DEALLOCATE Invoices1;
END;
GO
August 5, 2018 at 4:05 am
Arsh - Sunday, August 5, 2018 3:43 AMDear Friends,
I am new to TSQL programming . Need your help in making this SP work. The first test case I am using is 'What happens when the Cursor query returns no records'. Though there is not record returned when by this query ,the SP keeps running without doing anything instead of closing the cursor and exiting. Please advise where the problem is. I've copy pasted the code for the SP below .Thank you in advance.Though there are no records , the control goes in While loop which keeps running printing the inner PRINT after running the PRINT just before the WHILE once. Please advise why behaves this way
USE [JDE_DEVELOPMENT]
GO/****** Object: StoredProcedure [dbo].[ImportInvoices_Validations_CrossQueryCursor] Script Date: 05-Aug-18 12:27:13 PM ******/
SET ANSI_NULLS ON
GOSET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[ImportInvoices_ZeroRecords]
WITH EXECUTE AS OWNER
AS/****** Variable Declarations for [F03B11Z1] ******/
DECLARE
@ErrorCode NVARCHAR(40);/********* Cursor definition with outer join of [F03B11Z1] WITH [F0911Z1] ********/
DECLARE Invoices1 CURSOR FOR SELECT * FROM [JDEORIONDV].[PRODDTA].[F03B11Z1] I LEFT OUTER JOIN [JDEORIONDV].[PRODDTA].[F0911Z1] J ON I.VJEDUS = J.VNEDUS AND I.VJEDTN = J.VNEDTN AND I.VJEDLN = J.VNEDLN AND I.VJEDSP = J.VNEDSP AND I.VJEDBT = J.VNEDBT AND I.VJDOC = J.VNDOC AND I.VJDCT = J.VNDCT AND I.VJKCO = J.VNKCO AND I.VJSFX = J.VNSFX AND I.VJEDSP = 0 WHERE I.VJEDSP = 0; BEGIN
OPEN Invoices1
--LOOP UNTIL RECORDS ARE AVAILABLE.
PRINT 'I am just before while';
WHILE @@FETCH_STATUS = 0
PRINT 'I am in While';
SET @errorcode = '';
CLOSE Invoices1;
DEALLOCATE Invoices1;
END;
GO
Duplicate post, reply on this thread
😎
August 5, 2018 at 4:06 am
DECLARE @errorcode NVARCHAR(40);
/********* Cursor definition with outer join of [F03B11Z1] WITH [F0911Z1] ********/
DECLARE @Col1 int,
@Col2 nvarchar(5)
--...
DECLARE Invoices1 CURSOR FOR
SELECT *
FROM [JDEORIONDV].[PRODDTA].[F03B11Z1] I
LEFT OUTER JOIN [JDEORIONDV].[PRODDTA].[F0911Z1] J
ON I.VJEDUS = J.VNEDUS
AND I.VJEDTN = J.VNEDTN
AND I.VJEDLN = J.VNEDLN
AND I.VJEDSP = J.VNEDSP
AND I.VJEDBT = J.VNEDBT
AND I.VJDOC = J.VNDOC
AND I.VJDCT = J.VNDCT
AND I.VJKCO = J.VNKCO
AND I.VJSFX = J.VNSFX
AND I.VJEDSP = 0
WHERE I.VJEDSP = 0;
OPEN Invoices1
FETCH NEXT FROM Invoices1 INTO @Col1,@Col2
--LOOP UNTIL RECORDS ARE AVAILABLE.
PRINT 'I am just before while';
WHILE @@FETCH_STATUS = 0 BEGIN
PRINT 'I am in While';
-- do something with @Col1, @Col2...
SET @errorcode = '';
FETCH NEXT FROM Invoices1 INTO @Col1,@Col2
END
CLOSE Invoices1;
DEALLOCATE Invoices1;
GO
https://docs.microsoft.com/en-us/sql/t-sql/language-elements/declare-cursor-transact-sql?view=sql-server-2017
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply