Copy rows generator

if exists (select * from sysobjects where id = object_id(N'[dbo].[proc_copyrows]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[proc_copyrows]
GO

SET QUOTED_IDENTIFIER  OFF    SET ANSI_NULLS  ON 
GO

CREATE PROCEDURE proc_copyrows 
    @TableName as varchar(128),
    @FilterField as varchar(128) = null,
    @SourceValue as varchar(8000) = null,
    @DestinationValue as varchar(8000) = null,
    @SourceDB as varchar(128) = ''
as

    set nocount on 

    declare @ColumnName as varchar(128)
    declare @InsertSQL as varchar(8000)
    declare @SelectSQL as varchar(8000)
    declare @ConditionSQL as varchar(8000)
    declare @PkeySQL as varchar(8000)
    declare @strSQL as varchar(8000)
    declare @OrdinalPosition as int
    declare @LastKey as int
    declare @KeyCount as int
    declare @indexid int
    declare @inkey int

    create table #tmpresult(
        Result varchar(8000),
        result_no int IDENTITY(1, 1) PRIMARY KEY
    )

    --If no param in source or destination, remove filter
    if @SourceValue is null and @DestinationValue is null set @FilterField = null

    set @PKeySQL = ''
    set @LastKey = 0
    set @KeyCount = 0
    set @inkey = 0
    declare curKey cursor local read_only for
        select c.indid as index_id,b.name as column_name,b.colid as ordinal_position
        from sysobjects a 
        inner join syscolumns b on b.id=a.id and a.xtype='U' 
        inner join sysindexes c on c.id=a.id and (c.status & 6144)<>0
        inner join sysindexkeys d on d.id=a.id and d.indid=c.indid and d.colid=b.colid 
        where a.name=@TableName
        order by c.indid,b.colid

    open curKey

    fetch next from curKey into @indexid, @ColumnName, @OrdinalPosition
    
    while (@@fetch_status <> -1)
    begin
    if (@@fetch_status <> -2)
    begin
            if @LastKey<>@indexid
            begin
                if @LastKey = 0 
                begin
                    set @PKeySQL = '(('
                end
                else
                begin
                    set @PKeySQL = @PKeySQL + ') or ('
                end
                set @LastKey = @indexid
                set @KeyCount = 0
            end
            if isnull(@FilterField,'')<>@ColumnName or @DestinationValue is null
            begin
                if @KeyCount = 0
                begin
                    set @PKeySQL = @PKeySQL + 'z.[' + @ColumnName + ']=a.[' + @ColumnName + ']'
                end
                else
                begin
                    set @PKeySQL = @PKeySQL + ' and z.[' + @ColumnName + ']=a.[' + @ColumnName + ']'
                end
            end
            else
            begin
                if @KeyCount = 0
                begin
                    set @PKeySQL = @PKeySQL + 'z.[' + @ColumnName + ']=' + case when @DestinationValue='#null#' then 'null' else ''''+ @DestinationValue +'''' end
                end
                else
                begin
                    set @PKeySQL = @PKeySQL + ' and z.[' + @ColumnName + ']=' + case when @DestinationValue='#null#' then 'null' else ''''+ @DestinationValue +'''' end
                end
                set @inkey = 1
            end
            set @KeyCount = @KeyCount + 1

            fetch next from curKey into @indexid, @ColumnName, @OrdinalPosition
        end
    end
    close curKey
    deallocate curKey

    if @PKeySQL <> '' set @PKeySQL = @PKeySQL + '))'

    declare curColumn cursor local read_only for
        select e.name as column_name,e.colid as ordinal_position
        from sysobjects a 
        inner join syscolumns e on e.id=a.id and a.xtype='U' 
        where a.name=@TableName
        order by e.colid

    open curColumn

    set @ConditionSQL=''
    
    fetch next from curColumn into @ColumnName, @OrdinalPosition
    
    if @@fetch_status = 0 
    begin
        set @InsertSQL = 'insert into [' + @TableName + ']('
        set @SelectSQL = 'select '

        while (@@fetch_status <> -1)
        begin
        if (@@fetch_status <> -2)
        begin
                if @OrdinalPosition>1 
                begin
                    set @InsertSQL = @InsertSQL + ','
                    set @SelectSQL = @SelectSQL + ','
                end
                set @InsertSQL = @InsertSQL + '[' + @ColumnName + ']'
                if isnull(@FilterField,'') <>'' and @ColumnName = @FilterField and @DestinationValue is not null
                begin
                    set @SelectSQL = @SelectSQL + case when @DestinationValue='#null#' then 'null' else ''''+ @DestinationValue +'''' end
                end
                else
                begin
                    set @SelectSQL = @SelectSQL + 'a.[' + @ColumnName + ']'
                end
            end
            fetch next from curColumn into @ColumnName, @OrdinalPosition
        end

        set @InsertSQL = @InsertSQL + ') '
        insert #tmpresult(Result) select @InsertSQL

        insert #tmpresult(Result) select @SelectSQL

        set @SelectSQL = 'from '+@SourceDB+'[' + @TableName + '] a '
        insert #tmpresult(Result) select @SelectSQL

        if @SourceValue is not null and isnull(@FilterField,'')<>''
        begin
            set @ConditionSQL = @ConditionSQL + 'and a.[' + @FilterField + '] = ''' + @SourceValue + ''' '
        end
        if isnull(@FilterField,'')='' or @DestinationValue is null or @inkey <> 0
        begin
            set @ConditionSQL = @ConditionSQL + 'and not exists (select * from [' + @TableName + '] z where '+ @PkeySQL +') '
        end    
        else
        begin
            set @ConditionSQL = @ConditionSQL + 'and not exists (select * from [' + @TableName + '] z where z.[' + @FilterField + '] = ' + case when @DestinationValue='#null#' then 'null' else ''''+ @DestinationValue +'''' end + ' and '+ @PkeySQL +') '
        end    

        if @ConditionSQL <> '' 
        begin
            insert #tmpresult(Result) select 'where ' + substring(@ConditionSQL,5,8000)
        end    
    end

    close curColumn
    deallocate curColumn

    SELECT result FROM #tmpresult order by result_No

GO
SET QUOTED_IDENTIFIER  OFF    SET ANSI_NULLS  ON 
GO