1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59
|
CREATE procedure[dbo].[ReplaceRef](
@NameDataBase SYSNAME ,
@NameTable SYSNAME ,
@NameColumnRef SYSNAME,
@NameColumnTarget1 SYSNAME,
@NewTarget1 varchar(4000),
@STA_CODE SYSNAME ,
@NewTarget2 varchar(4000),
@NewCode varchar(4000)
)
BEGIN
declare @sql nvarchar(4000);
SET @sql = N'
UPDATE Target
SET
Target.IS_DELETE=1, Target.ID= Source.ID ,
Target.'+@STA_CODE+' ="'+@NewTarget2 +'",
Target.'+@NameColumnTarget1 +'="'+@NewTarget1+'"
FROM
'+@NameDataBase +'.dbo.'+@NameTable+' as Target ,
'+@NameDataBase+'.dbo.'+@NameTable+' as Source
WHERE
Target.ID IN
(SELECT tab2.ID
FROM '+@NameDataBase+' .dbo.'+@NameTable+' tab1, '+@NameDataBase+' .dbo.'+@NameTable+' tab2
WHERE tab2.'+@NameColumnRef+' like("%'+@NewTarget1+'%")
AND tab1.ID<>tab2.ID
AND tab1.ID=(SELECT MAX(ID) FROM '+@NameDataBase+' .dbo.'+@NameTable+' tab
where tab.'+@NameColumnRef+' like "%'+@NewTarget1+'%"))
and exists
(select COUNT(*) from '+@NameDataBase+' .dbo.'+@NameTable+'
where '+@NameColumnRef+' like("%'+@NewTarget1+'%")
and IS_DELETE!=1 group by IS_DELETE HAVING COUNT(*)>1
)
and Target.ID<>Source.ID
and Source.ID = (select max(ID) from '+@NameDataBase+' .dbo.'+@NameTable+' where '+@NameColumnRef+' like ("%'+@NewTarget1+'%") and IS_DELETE=0);
/*update new label for the unique station*/
UPDATE '+@NameDataBase+' .dbo.'+@NameTable+'
SET '+@STA_CODE+' ="'+@NewTarget2+'" ,'+@NameColumnTarget1+'="'+@NewTarget1+'"
where ID=(SELECT MAX(ID) FROM '+@NameDataBase+' .dbo.'+@NameTable+' tab
where tab.'+@NameColumnRef+' like "%'+@NewTarget1+'%") and IS_DELETE=0;
/*insert of list of STA_CODE */
IF OBJECT_ID("tempdb..#ListOfWord") IS NULL
CREATE TABLE #ListOfWord(
Word VARCHAR(64) COLLATE Latin1_General_CI_AI not null
)
insert into #ListOfSTA_CODE values("'+@NewTarget2+'" ,"'+@NewCode+'");';
SET QUOTED_IDENTIFIER OFF;
EXEC sp_executesql @sql;
END; |
Partager