I'm new to T-SQL; all my experience is in a completely different database environment (Openedge). I've learned enough to write the procedure below -- but also enough to know that I don't know enough!

This routine will have to go into a live environment soon, and it works, but I'm quite certain there are a number of c**k-ups and gotchas in it that I know nothing about.

The routine copies data from table A to table B, replacing the data in table B. The tables could be in any database. I plan to call this routine multiple times from another stored procedure. Permissions aren't a problem: the routine will be run by the dba as a timed job.

Could I have your suggestions as to how to make it fit best-practice? To bullet-proof it?

ALTER PROCEDURE [dbo].[copyTable2Table]
    @sdb        varchar(30),
    @stable     varchar(30),
    @tdb        varchar(30),
    @ttable     varchar(30),
    @raiseerror bit = 1,
    @debug      bit = 0
as
begin
    set nocount on

    declare @source      varchar(65)
    declare @target      varchar(65)
    declare @dropstmt    varchar(100)
    declare @insstmt     varchar(100)
    declare @ErrMsg      nvarchar(4000)
    declare @ErrSeverity int

    set @source      = '[' + @sdb + '].[dbo].[' + @stable + ']'
    set @target      = '[' + @tdb + '].[dbo].[' + @ttable + ']'
    set @dropStmt    = 'drop table ' + @target
    set @insStmt     = 'select * into ' + @target + ' from ' + @source
    set @errMsg      = ''
    set @errSeverity = 0

    if @debug = 1
        print('Drop:' + @dropStmt + '  Insert:' + @insStmt)

    -- drop the target table, copy the source table to the target
    begin try
        begin transaction    
        exec(@dropStmt)
        exec(@insStmt)
        commit
    end try

    begin catch
        if @@trancount > 0
            rollback

        select @errMsg      = error_message(), 
               @errSeverity = error_severity()

    end catch

    -- update the log table
    insert into HHG_system.dbo.copyaudit
        (copytime, copyuser, source, target, errmsg, errseverity)
        values( getdate(), user_name(user_id()), @source, @target, @errMsg, @errSeverity)

    if @debug = 1
        print ( 'Message:' + @errMsg + '  Severity:' + convert(Char, @errSeverity) )

    -- handle errors, return value
    if @errMsg <> ''
    begin 
        if @raiseError = 1
            raiserror(@errMsg, @errSeverity, 1)

        return 1
    end

    return 0
END

Thanks!