SQL Server Cursor Error: A cursor with the name db_cursorAsset already exists

Last Reply 2 months ago By Mudassar

Posted 2 months ago

ErrorMessage A cursor with the name db_cursorAsset already exists

-- =============================================
-- Author:		
-- Create date: 
-- Description:	Update the cost center of an order of an asset
-- =============================================
CREATE PROCEDURE [dbo].[spImportOrderCostCenterJobSelect]	
	@IDImportFile						typIDImportFile
AS
BEGIN	
	SET NOCOUNT ON;	
	
	DECLARE @SpName						VARCHAR(100) = OBJECT_NAME(@@PROCID)
	DECLARE @TranCount					INT = @@TRANCOUNT
	DECLARE @ReturnMessage				typReturnMessage
	
	DECLARE @IDImportOrder				[int] 
		
	DECLARE @OrderNumber				typBlob
    DECLARE @CostCenter					typTitle
    
	DECLARE @IDBlobItem					typIDBlobItem
	DECLARE @IDBlobItemOrder			typIDBlobItem
	DECLARE @UrlRandom					typUrlRandom
	DECLARE @IDCostCenter				typIDCostCenter
	DECLARE @IDCustomerSubContractor	typIDCustomer
	DECLARE @ProcessCode				typProcessCode
	DECLARE @ProcessDescription			typProcessDescription 
	DECLARE @ProcessDate				datetimeoffset(7)	
	DECLARE @IDLanguage					typIDLanguage
	DECLARE @IDWebSite					typIDWebSite
	DECLARE @IDZone						typIDZone
	DECLARE @IDSection					typIDSection
	DECLARE @IDLogin					typIDLogin	
	DECLARE @IDCustomerJoin				typIDCustomer
	DECLARE @IDCustomerSearch			TABLE (IDCustomer INT UNIQUE)

	BEGIN TRY
				
		-----------------------------
		-- LOGIN DATA
		-----------------------------	
		SELECT @IDWebSite = tif.IDWebSite, @IDZone =  tif.IDZone, @IDSection = tif.IDSection, @IDLogin = tif.IDLogin
			FROM tabImportFile AS tif 
		WHERE tif.IDImportFile = @IDImportFile
		
		SELECT @IDLanguage = ISNULL(tc.IDLanguage, 'ENG')
		FROM tabLogin AS tl 
			LEFT JOIN tabCustomer AS tc ON tc.IDCustomer = tl.IDCustomer
		WHERE tl.IDLogin = @IDLogin
		
		SET @IDCustomerJoin = dbo.sfBlobItemCustomerGet(@IDWebSite, @IDZone, @IDSection, @IDLogin, 'ORD')
		
		IF @IDCustomerJoin IS NOT NULL
			INSERT INTO @IDCustomerSearch (IDCustomer) SELECT IDCustomer FROM dbo.sfTableCustomer(@IDWebSite, @IDZone, @IDSection, @IDLogin, 'ORD')

		DECLARE db_cursor CURSOR LOCAL FOR
		SELECT tiocc.IDImportOrder, tiocc.OrderNumber, tiocc.CostCenter	       
		FROM tabImportOrderCostCenter AS tiocc			
			WHERE tiocc.IDImportFile = @IDImportFile
				AND tiocc.ProcessCode = 0
		ORDER BY tiocc.IDImportOrder
		
		OPEN db_cursor   
			FETCH NEXT FROM db_cursor INTO @IDImportOrder, @OrderNumber, @CostCenter
			
			WHILE @@FETCH_STATUS = 0   
			BEGIN										
				SET @ProcessDate = GETUTCDATE()
				--SET @IDBlobItem = NULL
				--SET @UrlRandom = NULL
				SET @IDCostCenter = NULL								
				--SET @IDCustomerSubContractor = NULL

				-----------------------------
				-- ORDER CHECK
				-----------------------------	
				IF @IDCustomerJoin IS NOT NULL
					SELECT @IDBlobItemOrder = tbi.IDBlobItem
					FROM tabBlobItem AS tbi
						INNER JOIN tabBlob AS tb ON tb.IDBlob = tbi.IDBlob
						INNER JOIN tabBusinessZoneProcess AS tbzp ON tbzp.IDBusinessZoneProcess = tbi.IDBusinessZoneProcess
						INNER JOIN tabBusinessZoneProcessWebSiteZoneSection AS tbzpwszs ON tbzpwszs.IDBusinessZoneProcess = tbzp.IDBusinessZoneProcess
							AND tbzpwszs.IDWebSite = @IDWebSite
							AND tbzpwszs.IDZone = @IDZone
							AND tbzpwszs.IDSection = @IDSection
						INNER JOIN tabProcess AS tp ON tp.IDProcess = tbzp.IDProcess
						INNER JOIN tabProcessType AS tpt ON tpt.IDProcessType = tp.IDProcessType
						INNER JOIN tabProcessStatus AS tps ON tps.IDProcess = tbi.IDProcess AND tps.IDStatus = tbi.IDStatus
						INNER JOIN tabStatus AS ts ON ts.IDStatus = tps.IDStatus
						INNER JOIN tabBlobItemOrder AS tbio ON tbio.IDBlobItem = tbi.IDBlobItem
						INNER JOIN @IDCustomerSearch AS ics ON ics.IDCustomer = tb.IDCustomer
					WHERE tpt.IDProcessTypeCategory = 'ORD'
						AND ts.IDStatusType = 1
						AND tb.Blob = @OrderNumber						
				ELSE
					SELECT @IDBlobItemOrder = tbi.IDBlobItem
					FROM tabBlobItem AS tbi
						INNER JOIN tabBlob AS tb ON tb.IDBlob = tbi.IDBlob
						INNER JOIN tabBusinessZoneProcess AS tbzp ON tbzp.IDBusinessZoneProcess = tbi.IDBusinessZoneProcess
						INNER JOIN tabBusinessZoneProcessWebSiteZoneSection AS tbzpwszs ON tbzpwszs.IDBusinessZoneProcess = tbzp.IDBusinessZoneProcess
							AND tbzpwszs.IDWebSite = @IDWebSite
							AND tbzpwszs.IDZone = @IDZone
							AND tbzpwszs.IDSection = @IDSection
						INNER JOIN tabProcess AS tp ON tp.IDProcess = tbzp.IDProcess
						INNER JOIN tabProcessType AS tpt ON tpt.IDProcessType = tp.IDProcessType
						INNER JOIN tabProcessStatus AS tps ON tps.IDProcess = tbi.IDProcess AND tps.IDStatus = tbi.IDStatus
						INNER JOIN tabStatus AS ts ON ts.IDStatus = tps.IDStatus
						INNER JOIN tabBlobItemOrder AS tbio ON tbio.IDBlobItem = tbi.IDBlobItem
					WHERE tpt.IDProcessTypeCategory = 'ORD'
						AND ts.IDStatusType = 1
						AND tb.Blob = @OrderNumber			

		
					IF @IDBlobItemOrder IS NULL
					BEGIN					
						SET @ProcessCode = -1 
						SET @ProcessDescription = dbo.sfDictionaryGet(CAST(@SpName + '01' AS VARCHAR(50)), @IDLanguage)
						GOTO NextRecord					
					END	

				
				-----------------------------
				-- ASSET CHECK
				-----------------------------	

				DECLARE db_cursorAsset CURSOR LOCAL FOR				
				SELECT tbi.IDBlobItem, tbi.UrlRandom, tbia.IDCustomerSubContractor
				FROM tabBlobItem AS tbi
					INNER JOIN tabBlob AS tb ON tb.IDBlob = tbi.IDBlob
					INNER JOIN tabBlobItemCross AS tbic ON tbic.IDBlobItem = tbi.IDBlobItem
					INNER JOIN tabBlobItem AS tbiFather ON tbiFather.IDBlobItem = tbic.IDBlobItemFather			
					INNER JOIN tabBusinessZoneProcess AS tbzp ON tbzp.IDBusinessZoneProcess = tbi.IDBusinessZoneProcess
					INNER JOIN tabBusinessZoneProcessWebSiteZoneSection AS tbzpwszs ON tbzpwszs.IDBusinessZoneProcess = tbzp.IDBusinessZoneProcess
						AND tbzpwszs.IDWebSite = @IDWebSite
						AND tbzpwszs.IDZone = @IDZone
						AND tbzpwszs.IDSection = @IDSection
					INNER JOIN tabProcess AS tp ON tp.IDProcess = tbzp.IDProcess
					INNER JOIN tabProcessType AS tpt ON tpt.IDProcessType = tp.IDProcessType
					INNER JOIN tabProcessStatus AS tps ON tps.IDProcess = tbi.IDProcess AND tps.IDStatus = tbi.IDStatus
					INNER JOIN tabStatus AS ts ON ts.IDStatus = tps.IDStatus
					INNER JOIN tabBlobItemAsset AS tbia ON tbia.IDBlobItem = tbi.IDBlobItem
				WHERE tpt.IDProcessTypeCategory = 'ASS'
					AND ts.IDStatusType = 1
					AND tbiFather.IDBlobItem = @IDBlobItemOrder						
				
				
				OPEN db_cursorAsset
					FETCH NEXT FROM db_cursorAsset INTO @IDBlobItem , @UrlRandom, @IDCustomerSubContractor 

				WHILE @@FETCH_STATUS = 0   
				BEGIN


				IF @IDBlobItem IS NULL
				BEGIN					
					SET @ProcessCode = -1 
					SET @ProcessDescription = dbo.sfDictionaryGet(CAST(@SpName + '02' AS VARCHAR(50)), @IDLanguage)
					GOTO NextRecord					
				END	
				
				IF @IDCustomerSubContractor IS NULL
				BEGIN					
					SET @ProcessCode = -1 
					SET @ProcessDescription = dbo.sfDictionaryGet(CAST(@SpName + '03' AS VARCHAR(50)), @IDLanguage)
					GOTO NextRecord					
				END	

				-----------------------------
				-- COST CENTER CHECK
				-----------------------------	
				IF @CostCenter IS NOT NULL
				BEGIN
					SELECT @IDCostCenter = tcc.IDCostCenter
					FROM tabCostCenter AS tcc
						LEFT JOIN tabCostCenterLanguage AS tccl ON tccl.IDCostCenter = tcc.IDCostCenter
					WHERE tcc.IDCustomer = @IDCustomerSubContractor
						AND ((tcc.CostCenterCode = @CostCenter) OR
							(tccl.CostCenterDescription = @CostCenter) OR 
							(tcc.CostCenterDescription = @CostCenter))
						AND tcc.[Deleted] = 0
												
					IF @IDCostCenter IS NULL
					BEGIN						
						SET @ProcessCode = -1 
						SET @ProcessDescription = dbo.sfDictionaryGet(CAST(@SpName + '04' AS VARCHAR(50)), @IDLanguage)
						GOTO NextRecord					
					END						
				END
								
				IF @TranCount = 0 BEGIN TRAN			
				ELSE SAVE TRAN @SpName

				EXEC spBlobItemUpdate
					@IDWebSite = @IDWebSite,
					@IDZone = @IDZone,
					@IDSection = @IDSection,
					@IDLogin = @IDLogin,
					@IDLanguage = @IDLanguage,
					@TypeUpdate = 'COSTCENTER',
					@IDBlobItem = @IDBlobItem,
					@UrlRandom = @UrlRandom,					
					@IDCostCenter = @IDCostCenter,
					@ReturnMessage = @ReturnMessage OUT
					
				IF @ReturnMessage <> 'OK'
				BEGIN				
					IF @TranCount > 0 ROLLBACK TRAN @SpName
					ELSE IF @TranCount = 0 ROLLBACK TRAN
					SET @ProcessCode = -1 
					SET @ProcessDescription = @ReturnMessage
					GOTO NextRecord
				END
				
					FETCH NEXT FROM db_cursorAsset INTO @IDBlobItem , @UrlRandom, @IDCustomerSubContractor 
				END
			
				CLOSE db_cursorAsset   
				DEALLOCATE db_cursorAsset 
				-----------------------------
											
				SET @ProcessCode = 1
				SET @ProcessDescription = 'OK'
				
				IF @TranCount = 0 COMMIT TRAN
				
				NextRecord:	
				
				UPDATE tabImportOrderCostCenter
				SET
					IDBlobItem = @IDBlobItemOrder,			
					ProcessCode = @ProcessCode,
					ProcessDescription = @ProcessDescription,
					ProcessDate = @ProcessDate
				WHERE IDImportOrder = @IDImportOrder				

				
				FETCH NEXT FROM db_cursor INTO @IDImportOrder, @OrderNumber, @CostCenter
			END
			
		CLOSE db_cursor   
		DEALLOCATE db_cursor 
		
		EXEC [spImportFileJobResult] 
			@IDWebSite = @IDWebSite, 
			@IDZone = @IDZone, 
			@IDSection = @IDSection, 
			@IDLogin = @IDLogin, 
			@IDLanguage = @IDLanguage, 
			@IDImportFile = @IDImportFile, 
			@thCol1 = 'Order', 
			@thCol2 = 'CostCenterCode', 
			@thCol3 = '',
			@Sql = 'DECLARE db_cursor CURSOR FOR  SELECT TOP 500 IDImportOrder, OrderNumber, CostCenter, NULL, ProcessCode, ProcessDescription, ProcessDate FROM tabImportOrderCostCenter WHERE IDImportFile = @IDImportFile ORDER BY ProcessCode, 1'
		
	END TRY
	
	BEGIN CATCH				
		DECLARE @xState INT = XACT_STATE()
		 
		IF @xState = -1
			ROLLBACK
		IF @xState = 1 AND @TranCount = 0
			ROLLBACK
		IF @xState = 1 AND @TranCount > 0
			ROLLBACK TRAN @SpName
			
		SET @ReturnMessage = ISNULL(ERROR_PROCEDURE(), '-') + ': ' + ERROR_MESSAGE()
		EXEC sysLogAdd @ErrorMessage = @ReturnMessage
	END CATCH
	    
END 
Posted 2 months ago

When you a cursor query and it fails then cursor is not deleted. Remove or delete the cursor by executing the drop query for cursor and then again execute.