Question about use of Cursors in combination with RETURN in a SQL Server 2008 Stored Procedure
Consider this example:
CREATE PROCEDURE [dbo].[test]
@ReturnEarly BIT = 0
AS
BEGIN
SET NOCOUNT ON
SELECT 1 AS Result INTO #Test
DECLARE @Result INT, @HasResult INT = 1
DECLARE TestCursor CURSOR FOR
SELECT Result
FROM #Test
WHERE Result = 0
OPEN TestCursor
FETCH NEXT FROM TestCursor INTO @Result
IF (@@FETCH_STATUS <> 0)
BEGIN
IF (@ReturnEarly = 1)
RETURN 0
ELSE
SET @HasResult = 0
END
CLOSE TestCursor
DEALLOCATE TestCursor
DROP TABLE #test --technically superflous
IF (@HasResult = 0)
RETURN 0
ELSE
RETURN 1 -- in reality many more checks on the actual results
END
GO
-- Allow DEALLOCATE CURSOR to be called
DECLARE @RC int, @ReturnEarly bit
SET @ReturnEarly = 1
EXECUTE @RC = [test] @ReturnEarly
PRINT @RC
GO
-- Exit before DEALLOCATE CURSOR is called
DECLARE @RC int, @ReturnEarly bit
SET @ReturnEarly = 0
EXECUTE @RC = [test] @ReturnEarly
PRINT @RC
GO
DROP PROCEDURE [dbo].[test]
Both EXEC calls of the Stored Procedure return the same result. My question is this: is there a potential leak associated with returning from inside the Cursor? There is not for the temporary table (#test), for example.
Bonus: Can you point me to docs suggesting either way? My google skills weren't up to it.
Extra Bonus: If there is a problem, is there any way I could find evidence of the leak? perhaps a dmv, event log, etc which would show the problem?
Thanks in advance!
Best Answer
Copying part of my answer from here...
How long the resources hang around depends on whether you declared the cursor locally or globally (and what the default is in your environment - default is global but you can change it).
If the cursor is global, then it can stay "alive" in SQL Server until the last piece of code is touched in the scope in which it was created - and can often persist even beyond that. For example if you call a stored procedure that creates a global cursor, then call 20 other stored procedures, the cursor will live on while those other 20 stored procedures are running, until the caller goes out of scope. I believe it will stay alive at the session level, not the connection level, but haven't tested this thoroughly. If the cursor is declared as local, then it should only stay in scope for the current object (but again, this is theoretical, and I haven't done extensive, low-level memory tests to confirm). I don't know of any DMVs that show this usage individually, or even if on a global level you could detect memory changes in DMVs that probably don't get updated often enough to reflect the truth at any microsecond.
The general concept, though, should be: when you're done with something, say so. I believe there are several ways to write your code such that the
RETURN
doesn't have to happen within the cursor, and probably ways to re-write the code to eliminate the cursor.In order to make my cursors as efficient as possible, I always use the following declarations:
I've also heard that there can be memory issues if you only
CLOSE
or onlyDEALLOCATE
so - even though I haven't proven or disproven it - I always do both when I'm done: