определение заявок для удаления и удаление их из прилинкованной базы MySQL в MSSQL

В примере использованы 2 курсора, приведен в качестве только примера!

Выглядит код в MSSQL так:

DECLARE @SQLtext nvarchar(max)
DECLARE @SQLmysql nvarchar(max)

---------- 1 -------------------Начало------------------
 SET @SQLtext = 'Select id from ticket where queue_id = 3' 
 SET @SQLtext = 'SELECT id FROM OPENQUERY(OTRS, '+ CHAR(39) + @SQLtext + CHAR(39) +') AS derivedtbl_1'

 create table #dataset([id] [bigint])
 insert #dataset exec (@SQLtext)

 DECLARE @id bigint
 DECLARE @id_a bigint
 DECLARE @MySQLT nvarchar(max)
 
 DECLARE #db CURSOR FOR Select id from #dataset order by id
 OPEN #db FETCH #db INTO @id
 while @@FETCH_STATUS <> -1 
 begin 
 DECLARE @idt nvarchar(20)
 SET @idt = CAST(@id as nvarchar(20))

 DROP TABLE #dataset_a

 SET @SQLtext = 'Select id from article where ticket_id = ' + @idt 
 SET @SQLtext = 'SELECT id FROM OPENQUERY(OTRS, '+ CHAR(39) + @SQLtext + CHAR(39) +') AS derivedtbl_1'
 create table #dataset_a([id] [bigint])
 insert #dataset_a exec (@SQLtext)
 DECLARE #db_a CURSOR FOR Select id from #dataset_a
 OPEN #db_a FETCH #db_a INTO @id_a
 while @@FETCH_STATUS <> -1 
 begin 
 DECLARE @T nvarchar(max)
 DECLARE @idt_a nvarchar(20)
 SET @idt_a = CAST(@id_a as nvarchar(20))
 SET @MySQLT = 'SELECT * FROM OPENQUERY(OTRS, '+ CHAR(39) + 'CALL `otrs`.`DeleteArticleByID`(#####);' + CHAR(39) +') AS derivedtbl_1'
 SET @T = REPLACE(@MySQLT,'#####', @idt_a )
 EXEC (@T)
 FETCH #db_a INTO @id_a 
 end 
 CLOSE #db_a DEALLOCATE #db_a
 DROP TABLE #dataset_a
 
 SET @MySQLT = 'SELECT * FROM OPENQUERY(OTRS, '+ CHAR(39) + 'CALL `otrs`.`DeleteTicketByID`(#####);' + CHAR(39) +') AS derivedtbl_1'
 SET @T = REPLACE(@MySQLT,'#####', @idt )
 EXEC (@T)
 FETCH #db INTO @id 
 end 
 CLOSE #db DEALLOCATE #db
---------- 1 --------------------окончание-----------------

 

Добавить комментарий

Ваш e-mail не будет опубликован. Обязательные поля помечены *