Home Microsoft SQL Server DigiMailing iRN Contact
    Keyword



sys.sp_MSexpandsubsnb

  No additional text.


Syntax

create procedure sys.sp_MSexpandsubsnb (@pubid uniqueidentifier)
AS
    declare @filterid int
    declare @base_nick int
    declare @join_nick int
    declare @qualified_basetable nvarchar(517)
    declare @unqualified_basetable nvarchar(270)
    declare @qualified_jointable nvarchar(517)
    declare @unqualified_jointable nvarchar(270)
    declare @join_clause nvarchar(4000)
    declare @retcode int
    declare @error int
    declare @rowcount int
    declare @base_nickstr nvarchar(10)
    declare @join_unique_key int

    /* get first filter to expand on */
    select @filterid = min(join_filterid) from dbo.sysmergesubsetfilters f, #notbelong nb
        where pubid = @pubid and nb.tablenick = f.join_nickname and f.join_filterid > nb.flag and (f.filter_type & 1) = 1

    while @filterid is not null
        begin
        /* get join clause and tables for this filter */
        select @join_nick = join_nickname, @join_clause = join_filterclause,
            @base_nick = art_nickname, @join_unique_key = join_unique_key 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 @retcode = sys.sp_MStablenamefromnick @base_nick, @qualified_basetable out, @pubid, @unqualified_basetable out
        if @@error<>0 or @retcode<>0 return(1)
        set @base_nickstr = convert(nchar(10), @base_nick)

        /* Mark rows so that we know we've expanded those rows for this filter */
        update #notbelong set flag = @filterid where flag < @filterid

        /* exec an insert/select query to expand #notbelong */
        exec ('insert into #notbelong (tablenick, rowguid, flag) select distinct ' +
                @base_nickstr + ', ' + @unqualified_basetable + '.RowGuidCol, 0 from ' + @qualified_basetable + ' ' + @unqualified_basetable +
                ', ' + @qualified_jointable + ' ' + @unqualified_jointable + ' where ( ' + @unqualified_jointable + '.RowGuidCol in
                    (select rowguid from #notbelong) ) and (' + @join_clause + ')')
        select @error=@@error, @rowcount=@@rowcount
        if @error<>0 return(1)
        /*
        ** if any rows inserted, try to contract the #notbelong table.
        ** Only join filters that are non unique need to contract the
        ** NOTBELONGS table 
        */
        if @rowcount <> 0 and @join_unique_key = 0
        begin
            exec @retcode = sys.sp_MScontractsubsnb @pubid, @base_nick, @qualified_basetable
            if @@error<>0 or @retcode<>0 return(1)
        end

        /* get next filter to expand with */
        select @filterid = min(join_filterid) from dbo.sysmergesubsetfilters f, #notbelong nb
            where pubid = @pubid and nb.tablenick = f.join_nickname and f.join_filterid > nb.flag and (f.filter_type & 1) = 1
        end

        return(0)

 
Last revision 2008RTM
See also

  sp_MSdelsubrows (Procedure)
sp_MSdelsubrowsbatch (Procedure)
sp_MSdrop_rlrecon (Procedure)
       



News

  Query a named instance
Sybase+ASE+silent+install
SQL 2008 R2
Deprecated procedures in SQL2008
Reporting Services item-level role definitions
Create all your missing indexes
Converting datetime field
Start MSSQL Server Profiler at time
Replicating MSSQL Server views
Exploring Microsoft Sharepoint
The OLE DB provider "SQLNCLI10" for linked server indicates.
Mobile solar charger
Oracle to SQL Server replication
Cannot insert the value NULL into column
Undocumented Microsoft SQL Server 2008
VMware
Zoekmachine optimalisatie
SQL Servers hidden objects
FckEditor
Flash in Ajax
De hype die AJAX heet
Barcode scannen
MySQL; Gratis, makkelijk en snel!
PHP
sIFR; de combinatie tussen HTML en Flash