Newbie动态存储过程--我如何改进它？

Question

我对to很陌生；我所有的经验都是在一个完全不同的数据库环境(Openedge)中进行的。我已经学到了足够多的东西来写下面的程序

这个例程很快就会进入到一个实际的环境中，而且它可以工作，但是我非常肯定它中有一些我不知道的c**的问题。

例程将数据从表A复制到表B，取代表B中的数据。我计划从另一个存储过程多次调用这个例程。权限不是问题:这个例程将由dba作为一个定时作业运行。

你能给我一些建议吗?如何使它适合最佳实践？为了防弹？

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

谢谢!

Answer 1

我是从Sybase的角度发言的(我不确定您是在使用SQLServer还是Sybase)，但我怀疑您在这两个环境中都会发现相同的问题。

首先，我要重复前面回答中关于假定的dbo对表的所有权的评论。然后，我会向您的DBA检查

• 是否会授予这个存储的proc在非

• 数据库中删除表的权限。在我的经验中，DBA讨厌这一点，而且由于潜在的灾难，很少将其作为一种选择。只有在使用选项

• 配置数据库时，才允许在事务中执行drop表之类的sp_dboption my_database, "ddl in tran", true操作。一般来说，在涉及DDL的事务中所做的事情应该非常短，因为它们将锁定诸如sysobjects这样的频繁引用的系统表，这样做会阻止其他数据服务器进程的进展。考虑到我们无法知道需要复制多少数据，它最终可能是一个非常长的事务，为每个人锁定了一段时间。更重要的是，DBA将需要在每个数据库上运行该命令，这些数据库中包含的表可能包含存储的proc的“@Target”表。如果要为事务使用事务，最好将其与处理数据insertion.

• While的任何事务分开--如果设置了ddl in tran选项，则可以在事务中执行drop table命令，在事务中不可能在事务中执行select * into。由于transaction.

• If是创建表和插入的组合，因此如果在@目标表中执行select * into，它将隐式地锁定数据库(如果有大量数据，则可能会在一段时间内)，因此您不能在不首先消除外键约束的情况下删除它。如果您有一个依赖于数字标识类型的“id”列(通常用作自动编号特性以生成替代项主键的值)，则

• ，请注意，您将无法将“@Source”表的“id”列中的值复制到“@Target”表的id列中。

• 我还将检查任何可能的数据库中事务日志的大小，这些数据库中可能包含“@Target”表的大小与任何可能的“@Source”表的大小有关。考虑到所有的复制都是在一个事务中完成的，那么您很可能会发现自己复制的表太大，以至于prod数据服务器中的事务日志会被吹灭，从而导致所有进程崩溃。我见过人们使用分块来在特别大的表上实现这一点，但是最后您需要将自己的检查放入代码中，以确保实际捕获了表的一致快照。

。

只是一个想法-如果这是用来获取快照，BCP怎么样？它可以用于转储表的内容，为您提供要查找的快照。如果您使用-c选项，您甚至可以以人类可读的形式获得它。

一切顺利，斯图尔特

Answer 2

这句话似乎有点危险：

set @dropStmt    = 'drop table ' + @target

如果目标表不存在怎么办？

我会设法保护这一点--就像：

set @dropStmt = 
    'if object_id(' + @target + ') IS NOT NULL   DROP TABLE ' + @target

这样，只有当对OBJECT_ID(tablename)的调用不返回NULL (即:表不存在)并且表被保证存在时，才发出DROP TABLE语句。

Answer 3

首先，替换所有代码，如

set @source = '[' + @sdb + '].[dbo].[' + @stable + ']'

有类似的代码

set @source = QuoteName(@sdb) + '.[dbo].' + QuoteName(@stable)

其次，您的过程假设所有对象都属于dbo --可能不是这样的。

第三，变量名在30个字符时太短- 128是sysname的长度。