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!