CODEWITHSUNDEEP
sql-servert-sql
Alag
Alag

Reputation: 1408

T-SQL Cursor never ends

I already searched with Google but I didn't find answer to my question. In the internet everything says that I do it correct. My problem is - I am trying for now write values what I saved to cursor (yes, only that). However loop which I use for it is infinity and it writes all values from cursor from start to end, after again and again and again ...

CREATE PROCEDURE CreateOrEditClient(...Parameters...) AS
DECLARE c CURSOR FOR SELECT column FROM table;
DECLARE @wrt VARCHAR(20);
DECLARE @tmp INT = 0;
 BEGIN
  OPEN c;
   FETCH NEXT FROM c INTO @wrt;
    WHILE @@FETCH_STATUS = 0
     BEGIN
      print CAST(@tmp AS VARCHAR(10)) + '  ' + @wrt;
      SET @tmp = @tmp +1;
      FETCH NEXT FROM c INTO @wrt;
     END;
  CLOSE c;
  DEALLOCATE c;
END;

EXEC CreateOrEditClient ...;

In my opinion cursor is written correct but output is:

0  790710/1112
1  900519/5555
2  790716/7877
....
19 111111/1111
0  790710/1112
1  900519/5555
....

and in the end it writes error message "Maximum stored procedure, function, trigger, or view nesting level exceeded (limit 32)."

If I use only 

SELECT column FROM table;

It writes only 20 records.

I know there is better solution for this example but I need to know why the Cursor doesn't work. It may be useful in future. Thanks for every asnwer.

Upvotes: 0

Views: 1663

Answers (4)

Alag
Alag

Reputation: 1408

Ok, in comments under question is solution.

"EXEC ProcedureName ...;" can't be in the same file like procedure's body when you compile.

CREATE PROCEDURE CreateOrEditClient(...Parameters...) AS
DECLARE c CURSOR FOR SELECT column FROM table;
DECLARE @wrt VARCHAR(20);
DECLARE @tmp INT = 0;
 BEGIN
  OPEN c;
   FETCH NEXT FROM c INTO @wrt;
    WHILE @@FETCH_STATUS = 0
 BEGIN
  print CAST(@tmp AS VARCHAR(10)) + '  ' + @wrt;
    SET @tmp = @tmp +1;
    FETCH NEXT FROM c INTO @wrt;
 END;
  CLOSE c;
  DEALLOCATE c;
END;

The example in question will call procedure recursively. Thanks to all.

Upvotes: 1

Deniyal Tandel
Deniyal Tandel

Reputation: 632

use 

 DECLARE @tmp INT
 SET @tmp=0

instad of DECLARE @tmp INT = 0

Upvotes: 0

Nux
Nux

Reputation: 10002

You would probably notice if you would keep your code indented correctly. You have:

CREATE PROCEDURE CreateOrEditClient(...Parameters...) AS
DECLARE c CURSOR FOR SELECT column FROM table;
DECLARE @wrt VARCHAR(20);
DECLARE @tmp INT = 0;
BEGIN
    OPEN c;
        FETCH NEXT FROM c INTO @wrt;
        WHILE @@FETCH_STATUS = 0
        BEGIN
            print CAST(@tmp AS VARCHAR(10)) + '  ' + @wrt;
            SET @tmp = @tmp +1;
            FETCH NEXT FROM c INTO @wrt;
        END;
    CLOSE c;
    DEALLOCATE c;
END;

You should have:

CREATE PROCEDURE CreateOrEditClient(...Parameters...) AS
BEGIN
    DECLARE @wrt VARCHAR(20);
    DECLARE @tmp INT = 0;
    DECLARE c CURSOR FOR SELECT column FROM table;
    OPEN c;
        FETCH NEXT FROM c INTO @wrt;
        WHILE @@FETCH_STATUS = 0
        BEGIN
            print CAST(@tmp AS VARCHAR(10)) + '  ' + @wrt;
            SET @tmp = @tmp +1;
            FETCH NEXT FROM c INTO @wrt;
        END;
    CLOSE c;
    DEALLOCATE c;
END;

EXEC CreateOrEditClient ...;

BTW. Did your code even run? Seems like a syntax error to me.

EDIT: Oh, and parameters are not declared in brackets AFAIK. For example:

CREATE PROCEDURE tmp_mol_cleanup_db
    @param1 type,
    @param2 type
AS
BEGIN
    -- procedure body
END;

Upvotes: 0

Javal Patel
Javal Patel

Reputation: 838

Please Try this ...

BEGIN
    SET NOCOUNT ON ;
    DECLARE @wrt VARCHAR(20)
    DECLARE @tmp INT = 0

    DECLARE Cur_Common CURSOR  FAST_FORWARD READ_ONLY FOR   
    SELECT iID FROM 'YourTable'
    OPEN Cur_Common
    FETCH NEXT FROM Cur_Common INTO @wrt

    WHILE @@FETCH_STATUS = 0 
        BEGIN
            print CAST(@tmp AS VARCHAR(10)) + '  ' + @wrt;
SET @tmp = @tmp +1;

       FETCH NEXT FROM Cur_Common INTO @wrt
        END
        CLOSE Cur_Common
    DEALLOCATE Cur_Common
END

Upvotes: 0

Related Questions