Cursor gets stack with runnable select

DECLARE @a uniqueidentifier, @b uniqueidentifier, @c datetime, @d numeric(15, 2) 
DECLARE MyCursor CURSOR FOR
SELECT Z.ID, Z.Name, Z.Date, (V1.Credit - V2.Debet) AS Money
FROM dbo.Table1 V1
INNER JOIN dbo.Table1 V2
ON V2.Name = V1.Name AND V2.ID = V1.ID AND V2.Date = V1.Date
INNER JOIN dbo.Table2 Z
ON Z.Name = V1.Name AND Z.ID = V1.ID AND Z.Date = V1.Date
WHERE V1.Date = DATEADD(Day, Z.AllowedDays, V2.Date)  
OPEN MyCursor
FETCH NEXT FROM MyCursor INTO @a, @b, @c, @d
    -- Get stuck here!!!
print 'My Cursor goes ahead'
WHILE @@FETCH_STATUS = 0
BEGIN
...

Can someone explain why does it get stuck in my case? I can't get the reason, because standalone Select is being completed itself.

Solution

You need to actually step within your loop, you do this by repeating the fetch inside the loop:

FETCH NEXT FROM MyCursor INTO @a

WHILE @@FETCH_STATUS = 0
BEGIN
    -- Some operations

    FETCH NEXT FROM MyCursor INTO @a
----^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
END

Otherwise, it's an infinite loop, constantly processing the first row.

As an aside, a cursor is very rarely the right answer; perhaps you should consider trying a set-based solution instead of a cursor. If you really need a cursor, at the very least, do this:

DECLARE MyCursor CURSOR LOCAL FAST_FORWARD FOR

If the query is spinning and spinning, then it's probably being blocked. This could be due to the cursor options, so trying LOCAL FAST_FORWARD may help, but it's probably more than that. We can't guess what you're being blocked by but you can check for yourself. Stop the query, and in that window, do SELECT @@SPID; and note the number that is returned. Now, start the cursor query again, and open a new window. Run the following (replacing x with the @@SPID number):

SELECT blocking_session_id, wait_type FROM sys.dm_exec_requests 
  WHERE session_id = x;

Now, if you get another session id in the first column, you can see what they are doing and why they are blocking you, using a similar query and a DBCC command:

SELECT status, command FROM sys.dm_exec_requests
  WHERE session_id = y;

DBCC INPUTBUFFER(y);

(Again, replacing y with the session id from the previous query.)