Need help on using cursor and displaying records in sql server Error :Msg 16916, Level 16, State 1

Question

I am trying to display the records form particular table using cursor. But its throwing this error.

Msg 16916, Level 16, State 1, Procedure testload, Line 26 [Batch Start Line 1]
A cursor with the name 'ShowSDFinfo' does not exist.

Msg 16916, Level 16, State 1, Procedure testload, Line 40 [Batch Start Line 1]
A cursor with the name 'ShowSDFinfo' does not exist.

Msg 16916, Level 16, State 1, Procedure testload, Line 62 [Batch Start Line 1]
A cursor with the name 'ShowSDFinfo' does not exist.

Msg 16916, Level 16, State 1, Procedure testload, Line 63 [Batch Start Line 1]
A cursor with the name 'ShowSDFinfo' does not exist.

Code:

CREATE OR ALTER PROCEDURE testload
AS
BEGIN
    SET NOCOUNT ON;

    DECLARE @VantiveOrgID1 VARCHAR(12),
            @VantiveCustomerName1 VARCHAR(12),
            @Sector1 VARCHAR(12)

    --DECLARE AND SET COUNTER.
    DECLARE @Counter INT
    SET @Counter = 1

    --DECLARE THE CURSOR FOR A QUERY.
    DECLARE ShowSFDinfo CURSOR STATIC LOCAL READ_ONLY FOR
        SELECT
            [Vantive OrgID],
            [Vantive Customer Name],                        
            [Sector]                
        FROM 
            [dbo].[SCASalesOrderExport_20180416] WITH (nolock)
        WHERE 
            date >= '1/1/18'
        ORDER BY
           date DESC;

   --OPEN CURSOR.
   OPEN ShowSDFinfo;

   --FETCH THE RECORD INTO THE VARIABLES.
   FETCH NEXT FROM ShowSDFinfo INTO @VantiveOrgID1, @VantiveCustomerName1, @Sector1

       --LOOP UNTIL RECORDS ARE AVAILABLE.

     WHILE @@FETCH_STATUS = 0
        BEGIN
            IF @Counter = 1
                BEGIN
             SELECT @@CURSOR_ROWS

                END

             FETCH NEXT FROM ShowSDFinfo INTO
@VantiveOrgID1
,@VantiveCustomerName1
,@Sector1

        END

           CLOSE ShowSDFinfo
   DEALLOCATE ShowSDFinfo

END

Hi and welcome to SO. Why are you using a cursor in the first place? They are horribly inefficient and should be avoided whenever possible. You have several other questionable things going on here. Your date string literals should be YYYYMMDD which is the ONLY date string format that works for any language setting. And NOLOCK....that carries a LOT of baggage with it. https://blogs.sentryone.com/aaronbertrand/bad-habits-nolock-everywhere/ — Sean Lange, May 18 '18 at 14:24
I want to close this as being a syntax error but there is so much room for improvement that I would hate for you to leave being satisfied that your code runs. — Sean Lange, May 18 '18 at 14:25
Hi Sean, I explain why i am doing this. First of all i have 8 Millions of data, I need to fetch those records and process through Alteryx tool.My ultimate aim is to make alteryx to fetch data faster. — Arjun R, May 18 '18 at 16:20
And i am completely new to this T-SQL, I am trying to display records by using cursors. If this is not the right way...Please suggest me the best way to display the records , So that Alteryx tool can process those data faster. — Arjun R, May 18 '18 at 16:23
8 million rows? That is way too much data to display. And a cursor would melt your sql server with that much in a cursor. No idea what Alteryx is but that is way too much for RBAR (row by agonizing row) processing. — Sean Lange, May 18 '18 at 16:31

score 2 · Answer 1 · answered May 18 '18 at 14:24

2

ShowSDFinfo and ShowSFDinfo are different cursor names!

DECLARE ShowSFDinfo CURSOR static local read_only 
  FOR..

and

DEALLOCATE ShowSDFinfo

answered May 18 '18 at 14:24

cloudsafe

2,444
1
8
24

score 0 · Answer 2 · answered May 18 '18 at 18:05

Instead of cursor try using the below. it will be faster than cursor for 8 million rows.

CREATE OR ALTER PROCEDURE testload
AS
BEGIN
    SET NOCOUNT ON;

  CEATE TABLE #TEMP ( ID INT IDENTITY(1,1),
    VantiveOrgID VARCHAR(12),
     VantiveCustomerName VARCHAR(12),
     Sector VARCHAR(12)

 DECLARE @cOUNT INT
DECLARE @I INT = 1


        SELECT
            [Vantive OrgID],
            [Vantive Customer Name],                        
            [Sector] 
        INTO #TEMP               
        FROM 
            [dbo].[SCASalesOrderExport_20180416] WITH (nolock)
        WHERE 
            date >= '1/1/18'
        ORDER BY
           date DESC;

SELECT @cOUNT = COUNT(*) FROM #TEMP

     WHILE (@I<=@cOUNT)
        BEGIN
            YOUR CODE 


END

Need help on using cursor and displaying records in sql server Error :Msg 16916, Level 16, State 1

2 Answers2