Home Microsoft SQL Server DigiMailing iRN Contact
    Keyword



sys.sp_MSscript_where_clause

  No additional text.


Syntax
create procedure sys.sp_MSscript_where_clause
(
    @objid int,
    @artid int,
    @clause_type varchar(15) = 'pk_new', -- 'new pk', 'old pk', 'upd version', 'upd rc', 'trg pk', 'qcft_comp', 'new_pk_q', 'subwins_check'
    @ts_col sysname = NULL,
    @indent int = 0,
    @op_type char(3) = NULL, -- 'ins', 'del', 'upd'
    @primary_key_bitmap varbinary(4000) = null
)
as
BEGIN
    declare @cmd            nvarchar(4000)
                ,@colname        sysname
                ,@ccoltype        sysname
                ,@spacer        nvarchar(20)
                ,@indkey        int
                ,@indid            int
                ,@key            sysname
                ,@rc            int
                ,@this_col        int
                ,@art_col        int
                ,@src_cols        int
                ,@total_col        int
                ,@col            sysname
                ,@qualname        nvarchar(517)
                ,@curparam        nvarchar(20)
                ,@retcode        int
                ,@fcreatedcolmap    bit
                ,@thisspname sysname
    declare @colmap table (relativeorder int identity(1,1), colid int)

    select @spacer = N' '
            ,@cmd = N''
            ,@indkey = 1
            ,@indid = 0
            ,@fcreatedcolmap = 0
            ,@thisspname = N'sp_MSscript_where_clause'
    exec sys.sp_MSget_qualified_name @objid, @qualname OUTPUT
    select @src_cols = max(column_id)
            ,@total_col = count(column_id)
    from sys.columns where object_id = @objid
    exec sys.sp_MSpad_command @cmd output, @indent
    select @cmd = case when (@clause_type = 'qcft_comp')
                then @cmd + N' N'' where '
                else @cmd + N'where' end
    exec sys.sp_MSflush_command @cmd output, 1, @indent
    
    -- process based on clause type
    
    if @clause_type in ('new pk','old pk','upd version','trg pk','version pk','qcft_comp','new_pk_q','subwins_check')
    begin
        if @primary_key_bitmap is null
        begin
            exec @indid = sys.sp_MStable_has_unique_index @objid
            if @indid is null
            begin
                raiserror(21750, 16, 1, @qualname)
                return (1)
            end
        end
        else
        begin
            
            -- PK bitmap is not null
            -- check if column Id match relative column order
            -- for specific trigger operations
            
            if ((@total_col < @src_cols) and (@clause_type = 'trg pk') and (@artid is null))
            begin
                
                -- this table may have altered columns, so when we need to
                -- set a mapping for using the bitmaps properly as the bitmap
                -- always refers relative column order
                
                insert into @colmap (colid)
                select column_id from sys.columns where object_id = @objid order by column_id
                if (@@error != 0)
                begin
                    raiserror(21499, 16, 1, @thisspname, 'populate', '@colmap', @@error)
                    return (1)
                end
                select @fcreatedcolmap = 1
            end
        end
        
        -- process each PK column
        
        while (1=1)
        begin
            
            -- get the column position
            
            if @primary_key_bitmap is null
            begin
                select @key = index_col(@qualname, @indid, @indkey)
                if @key is null
                    break
                exec sys.sp_MSget_col_position @objid, @artid, @key, @col output, NULL, 0, NULL, @this_col output
            end
            else
            begin
                exec sys.sp_MSget_map_position @primary_key_bitmap, @indkey, @col output, @this_col output
                if @this_col is null
break
                
                -- set the actual column id for this relative order in the PK bitmap if necessary
                
                if (@fcreatedcolmap = 1)
                begin
                    select @this_col = colid
                            ,@col = 'c' + convert(sysname, colid)
                    from @colmap
                    where relativeorder = @this_col
                end
            end
            
            -- Get column name
            
            exec @retcode = sys.sp_MSget_colinfo @objid, @this_col, @artid, 0, @key output, @ccoltype output
            if (@retcode = 1)
            begin
                
                -- this column not used for replication
                -- continue
                
                select @indkey = @indkey + 1
                continue
            end

            if @clause_type = 'new pk'
            begin
                if ColumnProperty(@objid, @key, 'IsIdentity') = 1
                    select @cmd = @cmd + @spacer + quotename(@key) + N' = @@identity'
                else
                    select @cmd = @cmd + @spacer + quotename(@key) + N' = @' + @col

                select @spacer = ' and '
            end
            else if @clause_type in ('upd version', 'subwins_check')
            begin
                select @cmd = @cmd + @spacer + quotename(@key) + N' = @' + @col + N'_old'
                select @spacer = ' and '
            end
            else if @clause_type = 'version pk'
            begin
                select @cmd = @cmd + @spacer + @qualname + '.' + quotename(@key) + N' = inserted.' + quotename(@key)
                select @spacer = ' and
    '
            end
            else if @clause_type in ('trg pk', 'old pk')
            begin
                if @op_type = 'ins'
                    select @cmd = @cmd + @spacer + quotename(@key) + N' = @' + @col + N'_old'
                else
                    -- The vars correspoding to pk were set in sp_MSscript
                    -- _pkvar_assignment.
                    select @cmd = @cmd + @spacer + quotename(@key) + N' = @' + @col

                    select @spacer = ' and
    '
            end
            else if (@clause_type = 'qcft_comp')
            begin
                
                -- Conflict compensation generation
                -- This is a special case - we generate
                -- and exec string for the WHERE clause
                
                select @curparam = case
                    when (@op_type = 'ins') then N'@' + @col
                    when (@op_type = 'del') then N'@' + @col + N'_old'
                    else N'ISNULL(@' + @col + N', @' + @col + N'_old)' end
                    ,@cmd = @cmd + @spacer + sys.fn_replreplacesinglequote(quotename(@key))

                if (lower(@ccoltype collate SQL_Latin1_General_CP1_CS_AS) = 'varchar')
                    select @cmd = @cmd + N' = '' + '''''''' + sys.fn_replreplacesinglequote(' + @curparam + N') collate database_default + '''''''' '
                else if (lower(@ccoltype collate SQL_Latin1_General_CP1_CS_AS) = 'nvarchar')
                    select @cmd = @cmd + N' = '' + ''N'''''' + sys.fn_replreplacesinglequote(' + @curparam + N') collate database_default + '''''''' '
                else if (lower(@ccoltype collate SQL_Latin1_General_CP1_CS_AS) = 'char')
                    select @cmd = @cmd + N' = '' + '''''''' + sys.fn_replreplacesinglequote(CAST(RTRIM(' + @curparam + N') as nvarchar)) collate database_default + '''''''' '
                else if (lower(@ccoltype collate SQL_Latin1_General_CP1_CS_AS) = 'nchar')
                    select @cmd = @cmd + N' = '' + ''N'''''' + sys.fn_replreplacesinglequote(CAST(RTRIM(' + @curparam + N') as nvarchar)) collate database_default + '''''''' '
                else if (lower(@ccoltype collate SQL_Latin1_General_CP1_CS_AS) in ('binary','varbinary'))
                    select @cmd = @cmd + N' = '' + sys.fn_varbintohexstr(' + @curparam + N') collate database_default '
                else if (lower(@ccoltype collate SQL_Latin1_General_CP1_CS_AS) in ('bit','bigint','int','smallint','tinyint','decimal','numeric'))
                    select @cmd = @cmd + N' = '' + CAST(' + @curparam + N' as nvarchar) '
                else if (lower(@ccoltype collate SQL_Latin1_General_CP1_CS_AS) in ('float','real'))
                    select @cmd = @cmd + N' = '' + CONVERT(nvarchar(60),' + @curparam + N', 2) '
                else if (lower(@ccoltype collate SQL_Latin1_General_CP1_CS_AS) in ('money','smallmoney'))
                    select @cmd = @cmd + N' = '' + CONVERT(nvarchar(40),' + @curparam + N', 2) '
                else if (lower(@ccoltype collate SQL_Latin1_General_CP1_CS_AS) = 'uniqueidentifier')
                    select @cmd = @cmd + N' = '' + '''''''' + CAST(' + @curparam + N' as nvarchar(40)) + '''''''' '
                else if (lower(@ccoltype collate SQL_Latin1_General_CP1_CS_AS) in ('datetime','smalldatetime'))
                    select @cmd = @cmd + N' = '' + '''''''' + CONVERT(nvarchar(40), ' + @curparam + N', 112) + N'' '' + CONVERT(nvarchar(40), ' + @curparam + N', 114) + '''''''' '
                else if (lower(@ccoltype collate SQL_Latin1_General_CP1_CS_AS) in ('date','time','datetime2','datetimeoffset'))
                    select @cmd = @cmd + N' = '' + '''''''' + CONVERT(nvarchar(40), ' + @curparam + N', 121) + '''''''' '
                else if (lower(@ccoltype collate SQL_Latin1_General_CP1_CS_AS) = 'sql_variant')
                    select @cmd = @cmd + N' = '' + sys.fn_sqlvarbasetostr(' + @curparam + N' ) collate database_default '
                else if (lower(@ccoltype collate SQL_Latin1_General_CP1_CS_AS) = 'xml')
                    select @cmd = @cmd + N' = '' + ''N'''''' + CAST(' + @curparam + N' as nvarchar(max)) + '''''''' '
                else
                    select @cmd = @cmd + N' = '' + CAST(' + @curparam + N' as nvarchar) '

                select @spacer = ' + '' and  '
            end
            else if @clause_type = 'new_pk_q'
            begin
                
                -- new value of primary key, ignore identity
                -- used for scripting in synctran procs
                
                select @cmd = @cmd + @spacer + quotename(@key) + N' = @' + @col
                select @spacer = ' and '
            end
            select @indkey = @indkey + 1
            -- flush command if necessary
            exec sys.sp_MSflush_command @cmd output, 1, @indent
        end -- end of while loop
        
        -- add version col as necessary
        
        if ((@clause_type in ('upd version','subwins_check')) and (@ts_col is not null))
        begin
            
            -- @ts_col is version col actually.
            -- check for special cases for queued processing
            
            exec sys.sp_MSget_col_position @objid, @artid, @ts_col, @col output
            select @cmd = case when (@clause_type = 'subwins_check')
                    then @cmd + @spacer + @ts_col + N' = @' + @col
                    else @cmd + @spacer + @ts_col + N' = @' + @col + N'_old' end
            
            -- save off command fragment
            
            exec sys.sp_MSflush_command @cmd output, 1, @indent
        end
    end -- end of if clause_type
    -- 'upd rc' is used for column value conflict detection. It is no longer used.
    else if @clause_type = 'upd rc'
    begin
        select @this_col = 1, @art_col = 1

        while @this_col <= @src_cols
        begin
            exec @rc = sys.sp_MSget_colinfo @objid, @this_col, @artid, 0, @colname output, @ccoltype output
            if @rc = 0
            begin
                select @cmd = @cmd +  @spacer + '(' + @colname + N' = @c' + convert(varchar(4), @this_col) + N'_old or ('
                select @cmd = @cmd + @colname + ' is null and @c' + convert(varchar(4), @this_col) + N'_old is null)) '
                select @spacer = N' and
    '
                exec sys.sp_MSflush_command @cmd output, 0, @indent
            end
            exec sys.sp_MSflush_command @cmd output, 1, @indent
            select @this_col = @this_col + 1
        end
        
        -- save off cmd fragment
        
        exec sys.sp_MSflush_command @cmd output, 1, @indent
    end
    
    -- all done
    
    return 0
END

 
Last revision 2008RTM
See also

  sp_MSscriptdelconflictfinder (Procedure)
sp_MSscriptinsertconflictfinder (Procedure)
sp_MSscriptupdateconflictfinder (Procedure)
sp_MSscript_compensating_insert (Procedure)
sp_MSscript_delete_statement (Procedure)
sp_MSscript_delete_subwins (Procedure)
sp_MSscript_endproc (Procedure)
sp_MSscript_insert_pubwins (Procedure)
sp_MSscript_pub_upd_trig (Procedure)
sp_MSscript_trigger_updates (Procedure)
sp_MSscript_trigger_version_updates (Procedure)
sp_MSscript_update_pubwins (Procedure)
sp_MSscript_update_statement (Procedure)
sp_MSscript_update_subwins (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