Why do I get "A cursor with the name already exists"?

SqlSql ServerTsqlDatabase Cursor

Sql Problem Overview


I have this trigger:

CREATE TRIGGER CHECKINGMAXQTYDAYSVACANCY
	ON TDINCI
AFTER INSERT 
AS
	DECLARE
		@incidentCode int,
		@dateStart datetime,
		@dateEnd datetime,
		@daysAccumulated int,
		@maxDaysAvailable int

	set @daysAccumulated = 0;

	select @incidentCode = CO_INCI from inserted;
	select @maxDaysAvailable = IN_DIAS_GANA from TCINCI
		where CO_INCI = @incidentCode;

	declare detailsCursor CURSOR FOR
		select FE_INIC, FE_FINA from TDINCI
		where CO_INCI = @incidentCode;

	open detailsCursor;
	
	if CURSOR_STATUS('variable', 'detailsCursor') >= 0
	begin
		fetch next from detailsCursor
			into @dateStart, @dateEnd;

		while @@FETCH_STATUS = 0
		begin
			set @daysAccumulated = @daysAccumulated + (DATEDIFF(DAY, @dateStart, @dateEnd) + 1);

			fetch next from detailsCursor
			into @dateStart, @dateEnd;
		end
		close detailsCursor;
		deallocate detailsCursor;
	end
	IF(@maxDaysAvailable > @daysAccumulated)
	BEGIN
		RAISERROR ('No se pueden ingresar mas dias de los programados en la cabecera de incidencias.', 16, 1);
		ROLLBACK TRANSACTION;
		RETURN 
	END
GO

When I do a Insert to the table TDINCI

INSERT INTO TDINCI 
VALUES (1, '20150101', '20150115', '2015-2015')

I get an error:

> A cursor with the name 'detailsCursor' already exists.

I open

open detailsCursor;

and close the cursor.

close detailsCursor;
deallocate detailsCursor;

Maybe there is something with the scope of cursor that I don't manage? Thanks in advance.

Sql Solutions


Solution 1 - Sql

You are using global cursor that will be defined each time you are calling this procedure and give you the same error.

Define a local cursor. Just put the keyword LOCAL after CURSOR:

declare detailsCursor CURSOR LOCAL FOR
...

Attributions

All content for this solution is sourced from the original question on Stackoverflow.

The content on this page is licensed under the Attribution-ShareAlike 4.0 International (CC BY-SA 4.0) license.

Content TypeOriginal AuthorOriginal Content on Stackoverflow
QuestionErick Asto OblitasView Question on Stackoverflow
Solution 1 - SqlAhmadView Answer on Stackoverflow