Home Microsoft SQL Server DigiMailing iRN Contact
    Keyword



sys.sp_replscriptuniquekeywhereclause

  No additional text.


Syntax
create procedure sys.sp_replscriptuniquekeywhereclause
(
    @tabid int                                  -- id of the table
    ,@artid int                                 -- id of the article
    ,@prefix nvarchar(10) = '@c'       -- prefix for the scripted column variables
    ,@suffix nvarchar(10) = null	   -- suffix for the scripted column variables
    ,@mode tinyint
    -- 1 = insert custom proc,
    -- 2 = upd custom proc non PK (VCALL),
    -- 3 = upd custom proc non PK (XCALL),
    -- 4 = compensating where all keys,
    -- 5 = compensating where non PK keys,
    -- 6 = refresh cursor all keys,
    -- 7 = refresh cursor non PK keys
    -- 8 = compensating where PK only
    -- 9 = upd custom proc PK only (XCALL),
    ,@paramcount int = null              -- Total number of parameters - needed for mode = 2 and 3
    ,@skipindexesonudtcolumns bit = 0
 )
as
begin
    set nocount on
    declare @retcode int
                ,@indid int
                ,@is_primary_key bit
                ,@indkey int
                ,@qualname nvarchar(517)
                ,@colname sysname
                ,@var sysname
                ,@artcol int
                ,@thiscol int
                ,@cmd nvarchar(4000)
                ,@findexstarted bit
                ,@fisfirstindex bit
                ,@fskipcomputedcols bit
                ,@emptywhereclauselength int
    
    -- constants
    
                ,@modeinscustproc tinyint
                ,@modeupdcustprocnonpkvcall tinyint
                ,@modeupdcustprocnonpkxcall tinyint
                ,@modecompensatingallkeys tinyint
                ,@modecompensatingnonpkkeys tinyint
                ,@modecompensatingpkonly tinyint
                ,@moderefreshcursordeclareallkeys tinyint
                ,@moderefreshcursordeclarenonpkkeys tinyint
                ,@modeupdcustprocpkonlyxcall tinyint

    
    -- initialize
    
    select @modeinscustproc = 1
            ,@modeupdcustprocnonpkvcall = 2
            ,@modeupdcustprocnonpkxcall = 3
            ,@modecompensatingallkeys = 4
            ,@modecompensatingnonpkkeys = 5
            ,@moderefreshcursordeclareallkeys = 6
            ,@moderefreshcursordeclarenonpkkeys = 7
            ,@modecompensatingpkonly = 8
            ,@modeupdcustprocpkonlyxcall = 9
    
    -- security check - should be dbo or sysadmin
    
    exec @retcode = sp_MSreplcheck_publish
    if @@error != 0 or @retcode != 0
        return (1)
    
    -- process if the object is a table and has index
    
    if (ObjectProperty(@tabid, 'IsTable') != 1) or (ObjectProperty(@tabid, 'TableHasIndex') != 1)
        return (1)
    
    -- Get the qualified name of the table
    
    exec @retcode = sp_MSget_qualified_name @tabid, @qualname OUTPUT
    if @@error != 0 or @retcode != 0 or @qualname is null
        return (1)
    
    -- @artid cannot be null
    
    if (@artid is null)
        return (1)
    
    -- Check @mode
    
    if (@mode not in (@modeinscustproc, @modeupdcustprocnonpkvcall, @modeupdcustprocnonpkxcall,
                                @modeupdcustprocpkonlyxcall, @modecompensatingallkeys, @modecompensatingnonpkkeys,
                                @moderefreshcursordeclareallkeys, @moderefreshcursordeclarenonpkkeys,
                                @modecompensatingpkonly))
    begin
        return (1)
    end
    
    -- validate @paramcount
    
    if ((@mode in (@modeupdcustprocnonpkxcall,@modeupdcustprocpkonlyxcall)) and (@paramcount is null))
    begin
        return (1)
    end
    
    -- enumerate indices
    -- The scripting will be done as follows :
    -- A) all keys will include PK and all unique keys
    --      where (pk1 = @cv and pk2 = @cw ...) or (ui1k1 = @cx and ui1k2 = @cy ...) or (u2k1 = @cz and ...) ...
    -- B) non PK keys will use only the unique keys that are not part of PK
    --      where (ui1k1 = @cx and ui1k2 = @cy ...) or (u2k1 = @cz and ...) ...
    
    select @cmd = case when (@mode in (@modecompensatingallkeys, @modecompensatingnonpkkeys, @modecompensatingpkonly))
                            then N' N'' where '
                            else N' where ' end
            ,@findexstarted = 0
            ,@fisfirstindex = 1
            ,@fskipcomputedcols = 1
    set @emptywhereclauselength = datalength(@cmd)
    declare #hcindid cursor local fast_forward for
        select index_id, is_primary_key
          from sys.indexes indexes
         where object_id = @tabid
           and is_unique = 1
           and index_id > 0 and index_id < 255
           and (@skipindexesonudtcolumns = 0 or not exists (select index_columns.index_id
                                                              from sys.index_columns index_columns
                                                        inner join sys.columns columns
                                                                on index_columns.object_id = columns.object_id
                                                               and index_columns.index_id = indexes.index_id
                                                               and index_columns.column_id = columns.column_id
                                                               and index_columns.object_id = @tabid
                                                        inner join sys.types types
                                                                on columns.system_type_id = types.system_type_id
                                                               and columns.user_type_id = types.user_type_id
                                                               and types.is_assembly_type = 1))
           order by index_id asc
    open #hcindid
    fetch #hcindid into @indid, @is_primary_key
    while (@@fetch_status != -1)
    begin
        
        -- If we are in (@modeupdcustprocnonpkvcall, @modeupdcustprocnonpkxcall, @modecompensatingnonpkkeys,
        -- @moderefreshcursordeclarenonpklkeys) mode then skip processing the PK index.
        -- If we are in (@modeupdcustprocpkonlyxcall, @modecompensatingpkonly) mode
        -- skip processing the non PK index
        
        if ((@mode in (@modeupdcustprocnonpkvcall, @modeupdcustprocnonpkxcall, @modecompensatingnonpkkeys,
                    @moderefreshcursordeclarenonpkkeys)) and (@is_primary_key = 1)
            or (@mode in (@modeupdcustprocpkonlyxcall, @modecompensatingpkonly))
                and (@is_primary_key = 0))
        begin
            
            -- fetch next unique index
            
            fetch #hcindid into @indid, @is_primary_key
            continue
        end
        
        -- Enumerate the keys in this index
        
        select @indkey = 1
        while (@indkey <= 16)
        begin
            
            -- get the column name for the key
            
            select @colname = index_col(@qualname, @indid, @indkey)
            if (@colname is null)
                break
            
            -- check if this column is enabled for replication
            
            select @artcol = 0
            exec sp_MSget_col_position @tabid, @artid, @colname, NULL, @artcol output, 0, NULL, @thiscol output, @fskipcomputedcols
            if (@artcol > 0)
            begin
                
                -- check if we are scripting the first key for this index
                
                if (@findexstarted = 1)
                begin
                    select @cmd = case when (@mode in (@modecompensatingallkeys, @modecompensatingnonpkkeys, @modecompensatingpkonly))
                                            then @cmd + N' + N'' and '
                                            else @cmd + N' and ' end
                end
         else
                begin
                    
                    -- check if we are scripting the first index.
                    
                    if (@fisfirstindex = 0)
                    begin
                        select @cmd =  @cmd + N' or '
                    end
                    else
                    begin
                        select @fisfirstindex = 0
                    end
                    
                    -- set @findexstarted while processing the first key
                    
                    select @findexstarted = 1
                    select @cmd = @cmd + N' ( '
                end
                
                -- script this column
                
                if (@mode in (@modecompensatingallkeys, @modecompensatingnonpkkeys, @modecompensatingpkonly))
                begin
                    
                    -- scripting a delete compensating command for synctran proc - we are building a dynamic string
                    -- case when @c is null then '[col] is null' else '[col] = @c' end
                    
                    declare @ccoltype sysname

                    select @var = @prefix + cast(@thiscol as nvarchar(10))
                    if (@suffix is not null)
                        select @var = @var + @suffix
                    select @cmd = @cmd + N''' + case when ( ' + @var + N' is null) then  N'' ' + sys.fn_replreplacesinglequote(quotename(@colname)) + N' is null '' else N'' ' + sys.fn_replreplacesinglequote(quotename(@colname))
                    exec sp_MSget_colinfo @tabid, @thiscol, @artid, 0, NULL, @ccoltype output
                    if (lower(@ccoltype collate SQL_Latin1_General_CP1_CS_AS) = 'varchar' )
                        select @cmd = @cmd + N' = '''''' + CAST( sys.fn_replreplacesinglequote(' + @var + N') collate database_default as varchar) + '''''''' end '
                    else if (lower(@ccoltype collate SQL_Latin1_General_CP1_CS_AS) = 'nvarchar')
                        select @cmd = @cmd + N' = N'''''' + sys.fn_replreplacesinglequote(' + @var + N') collate database_default + '''''''' end '
                    else if (lower(@ccoltype collate SQL_Latin1_General_CP1_CS_AS) = 'char')
                        select @cmd = @cmd + N' = '''''' + sys.fn_replreplacesinglequote(CAST(RTRIM(' + @var + N') as nvarchar)) collate database_default + '''''''' end '
                    else if (lower(@ccoltype collate SQL_Latin1_General_CP1_CS_AS) = 'nchar')
                        select @cmd = @cmd + N' = N'''''' + sys.fn_replreplacesinglequote(CAST(RTRIM(' + @var + N') as nvarchar)) collate database_default + '''''''' end '
                    else if (lower(@ccoltype collate SQL_Latin1_General_CP1_CS_AS) in ('binary','varbinary'))
                        select @cmd = @cmd + N' = '' + sys.fn_varbintohexstr(' + @var + N') collate database_default end '
                    else if (lower(@ccoltype collate SQL_Latin1_General_CP1_CS_AS) in ('bit','bigint','int','smallint','tinyint','decimal','numeric'))
                        select @cmd = @cmd + N' = '' + CAST( ' + @var + N' as nvarchar) end '
                    else if (lower(@ccoltype collate SQL_Latin1_General_CP1_CS_AS) in ('float','real'))
                        select @cmd = @cmd + N' = '' + CONVERT(nvarchar(60), ' + @var + N' , 2) end '
                    else if (lower(@ccoltype collate SQL_Latin1_General_CP1_CS_AS) in ('money','smallmoney'))
                        select @cmd = @cmd + N' = '' + CONVERT(nvarchar(40), ' + @var + N' , 2) end '
                    else if (lower(@ccoltype collate SQL_Latin1_General_CP1_CS_AS) = 'uniqueidentifier')
                        select @cmd = @cmd + N' = '''''' + CAST( ' + @var + N' as nvarchar(40)) + '''''''' end '
                    else if (lower(@ccoltype collate SQL_Latin1_General_CP1_CS_AS) in ('datetime','smalldatetime'))
                        select @cmd = @cmd + N' = '''''' + CONVERT(nvarchar(40), ' + @var + N', 112) + N'' '' + CONVERT(nvarchar(40), ' + @var + N', 114) + '''''''' end '
                    else if (lower(@ccoltype collate SQL_Latin1_General_CP1_CS_AS) in ('date','time', 'datetime2', 'datetimeoffset'))
                        select @cmd = @cmd + N' = '''''' + CONVERT(nvarchar(40), ' + @var + N', 121) + '''''''' end '
                    else if (lower(@ccoltype collate SQL_Latin1_General_CP1_CS_AS) = 'sql_variant')
                        select @cmd = @cmd + N' = '' + sys.fn_sqlvarbasetostr( ' + @var + N' ) collate database_default  end '
                    else if (lower(@ccoltype collate SQL_Latin1_General_CP1_CS_AS) = 'xml')
                        select @cmd = @cmd + N' = N'''''' + CAST(' + @var + N' as nvarchar(max)) + '''''''' end '
                    else
                        select @cmd = @cmd + N' = '' + CAST( ' + @var + N' as nvarchar) end '
                end
                else
                begin
                    
                    -- scripting for custom procs - static script
                    -- For publisher scripting - use @thiscol
                    -- For custom proc scripting - use @artcol
                    -- For Update custom proc scripting special cases
                    --  for XCALL - use variation of @artcol
                    --  for VCALL - use @artcol
                    
                    select @var = case when (@mode in (@modeupdcustprocnonpkxcall,@modeupdcustprocpkonlyxcall))
                                                    then @prefix + cast((@artcol + @paramcount/2) as nvarchar(10))
                                                when (@mode in (@moderefreshcursordeclareallkeys, @moderefreshcursordeclarenonpkkeys))
                                                    then @prefix + cast(@thiscol as nvarchar(10))
                                                else @prefix + cast(@artcol as nvarchar(10)) end
                    if (@suffix is not null)
                        select @var = @var + @suffix
                    
                    -- Does the column allow NULLs
                    
                    if (columnproperty( @tabid , @colname , 'AllowsNull' ) = 1)
                    begin
                        
                        -- static scripting should handle NULL valued column
                        -- ((<@var> is null and  is null) or (<@var> is not null and  = <@var>))
                        
                        select @cmd = @cmd + N'((' + @var + N' is null and ' + quotename(@colname) + N' is null) or ('
                                                + @var + N' is not null and ' + quotename(@colname) + N' = ' + @var + N'))'
                    end
                    else
                    begin
                        
                        -- static scripting does not need to check for NULL values
                        --  = <@var>
                        
                        select @cmd = @cmd + quotename(@colname) + N' = ' + @var
                    end
                    
                    -- special processing for @modeupdcustprocnonpkvcall, @modeupdcustprocnonpkxcall
                    
                    if (@mode = @modeupdcustprocnonpkvcall)
                    begin
                        declare @bytepos int
                                    ,@bitpos int
                        select @bytepos = 1 + (@artcol-1) / 8
                                ,@bitpos = power(2, (@artcol-1) % 8 )
                        select @cmd = @cmd + N' and (( substring(@bitmap,' + cast(@bytepos as nvarchar(10))
                                        + N',' + cast(@bytepos as nvarchar(10)) + N') & ' + cast(@bitpos as nvarchar(10)) + N') = '
                                        + cast(@bitpos as nvarchar(10)) + N') '
                    end
                    else if (@mode = @modeupdcustprocnonpkxcall)
                    begin
                        select @cmd = @cmd + N' and ' + @prefix + cast((@artcol + @paramcount/2) as nvarchar(10))
                        if (@suffix is not null)
                            select @cmd = @cmd + @suffix
                        select @cmd = @cmd + N' != ' + @prefix + cast(@artcol as nvarchar(10))
                        if (@suffix is not null)
                            select @cmd = @cmd + @suffix
                    end
                end
                
                -- transfer command string to table if too large
                
                if (len(@cmd) > 3000)
                    exec sp_MSflush_command @cmd output, 1, 0
            end
            
            -- get the next key for the index
            
            select @indkey = @indkey + 1
        end
        
        -- done with current index
        
        if (@findexstarted = 1)
        begin
            select @findexstarted = 0
                    ,@cmd = case when (@mode in (@modecompensatingallkeys, @modecompensatingnonpkkeys, @modecompensatingpkonly))
                                        then @cmd + N' + '' ) '
                                        else @cmd + N' ) ' end
        end
        
        -- fetch next unique index
        
        fetch #hcindid into @indid, @is_primary_key
    end
    close #hcindid
    deallocate #hcindid

    if @mode in (@modeinscustproc,@modeupdcustprocnonpkvcall,@modeupdcustprocnonpkxcall) and datalength(@cmd) = @emptywhereclauselength
    begin
        set @cmd = @cmd + N'(0=1)'
    end

    
    -- Final flush
    
    if (@mode in (@modecompensatingallkeys, @modecompensatingnonpkkeys, @modecompensatingpkonly))
        select @cmd = @cmd + N''''
    if (len(@cmd) > 0)
        exec sp_MSflush_command @cmd output, 1, 0
    
    -- all done
    
    return 0
end

 
Last revision 2008RTM
See also

  sp_MSdrop_repltran (Procedure)
sp_MSscriptupdateconflictfinder (Procedure)
sp_MSscript_compensating_insert (Procedure)
sp_MSscript_delete_pubwins (Procedure)
sp_MSscript_insert_pubwins (Procedure)
sp_MSscript_insert_subwins (Procedure)
sp_MSscript_update_pubwins (Procedure)
sp_MSscript_update_subwins (Procedure)
sp_scriptinsproccore (Procedure)
sp_scriptupdproccore (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