create procedure sys.sp_MScontractsubsnb
(@pubid uniqueidentifier,
@tablenick int,
@basetable nvarchar(517))
AS
declare @filter_clause nvarchar(4000)
declare @join_nick int
declare @qualified_jointable nvarchar(517)
declare @unqualified_jointable nvarchar(270)
declare @filterid int
declare @retcode int
declare @tablenickstr nvarchar(10)
declare @unqual_basetable sysname
declare @command nvarchar(max)
set @tablenickstr = convert(nchar(10), @tablenick)
select @unqual_basetable = name from sys.objects where object_id=object_id(@basetable)
/* First, try to remove rows from notbelong based on the article filter, if there is one */
select @filter_clause = subset_filterclause from dbo.sysmergearticles where
pubid = @pubid and nickname = @tablenick
if len(@filter_clause) > 0
begin
exec ('delete from #notbelong with (paglock) where tablenick = ' + @tablenickstr + ' and rowguid in
(select RowGuidCol from ' + @basetable + ' where ' + @filter_clause + ')' )
if @@error<>0 return(1)
end
/* Now loop over any join filters that have this as the base_table */
select @filterid = min(join_filterid) from dbo.sysmergesubsetfilters
where pubid = @pubid and art_nickname = @tablenick and (filter_type & 1) = 1
while @filterid is not null
begin
/* Get joining table and filter clause */
select @join_nick = join_nickname, @filter_clause = join_filterclause
from dbo.sysmergesubsetfilters where pubid = @pubid and join_filterid = @filterid and (filter_type & 1) = 1
exec @retcode = sys.sp_MStablenamefromnick @join_nick, @qualified_jointable out, @pubid, @unqualified_jointable out
if @@error<>0 or @retcode<>0 return(1)
/* Exec query to remove rows from #notbelong that still belong to partial */
select @command = 'delete from #notbelong with (paglock) where tablenick = ' + @tablenickstr + ' and rowguid in
(select ' + quotename(@unqual_basetable) + '.RowGuidCol from ' + @basetable + ' ' + quotename(@unqual_basetable) + ', ' +
@qualified_jointable + ' ' + @unqualified_jointable + ' where (' + @filter_clause + ') and ' + @unqualified_jointable +
'.RowGuidCol not in (select rowguid from #notbelong))'
exec(@command)
if @@error<>0 return(1)
/* Find the next filter that might apply */
select @filterid = min(join_filterid) from dbo.sysmergesubsetfilters
where pubid = @pubid and art_nickname = @tablenick and join_filterid > @filterid and (filter_type & 1) = 1
end
return(0)