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.

Answers


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.


Need Your Help

Bayesian filtering for spam

artificial-intelligence email-spam bayesian

I was wondering if there is any good and clean oo implementation of bayesian filtering for spam and text classification? For learning purposes.

About UNIX Resources Network

Original, collect and organize Developers related documents, information and materials, contains jQuery, Html, CSS, MySQL, .NET, ASP.NET, SQL, objective-c, iPhone, Ruby on Rails, C, SQL Server, Ruby, Arrays, Regex, ASP.NET MVC, WPF, XML, Ajax, DataBase, and so on.