主页 / dba / 问题 / 7422
Accepted
bernd_k
Asked: 2011-11-01 05:21:11 +0800 CST

有没有办法只在定义更改时替换 SQL Server 存储过程？

  • 772

我知道以下两种模式来编写用于创建存储过程的脚本，可以重复执行而不会引发错误。

if object_id('my_proc') > 0 drop procedure my_proc
go
create procedure dbo.my_proc as Print 'This is not a dummy';

另一个保留权限

if object_id('my_proc') is null
    EXEC ('create procedure dbo.my_proc as Print ''This is a dummy''');
go
ALTER PROCEDURE dbo.my_proc as Print 'This is not a dummy';

我猜当程序存在并且它的哈希码与新版本的相同时，就不需要删除并重新创建或更改程序。

我的问题是HashBytes被限制为最大 8000 字节，我不能像一般使用它一样

if object_id('my_proc') is null
    EXEC ('create procedure dbo.my_proc as Print ''This is a dummy''');
go
if object_Id('my_proc') > 0
and
(
Select HashBytes('MD5',definition) MD5
from sys.sql_modules m
join sysobjects o on o.id = m.object_id
where o.name = 'my_proc'
) <> 0x9028A1B9D93AC7592EC939CCABF9D3DE
begin
    print 'definition has changed';
    EXEC ('ALTER PROCEDURE dbo.my_proc as Print ''This is not a dummy''');
end

对于定义超过 4000 个字符的过程。有什么建议以类似的方式处理这些案件吗？

编辑：

我不仅要避免刷新缓存计划。

我还必须应对具有不同版本的存储过程的不同客户，我只想用更新的版本替换其中一个变体。

sql-server stored-procedures

3 个回答

  1. 不应将权限分配给存储过程

    您的存储过程应该位于架构中，权限在架构上（授予角色）。这样，该架构中的新对象自动拥有权限。没有单个对象应该有权限

    所以无论哪种方式都可以。

    请参阅这些其他 DBA 问题：

    仅供参考，object_id 是有符号的 int所以使用“不等于”

    if object_id('my_proc') <> 0 drop procedure my_proc

    编辑， nvarchar(max) 可以直接比较

    if object_Id('my_proc') <> 0
    drop proc ThenewProc
GO
CREATE PROC ThenewProc
...
GO

IF object_definition(object_id('my_proc')) <> 
         object_definition(object_id('ThenewProc')) <> 0
BEGIN
    drop procedure my_proc
    drop proc ThenewProc
END
GO
CREATE PROC my_proc
...
GO
    • 6
  2. Best Answer

    我们最近在我的工作场所进行了这个讨论。

    首先，我想赞扬您通过使用HASHBYTES()overCHECKSUM()检测更改来做“正确的事情”。微软特别警告不要用于CHECKSUM(@input)此目的，因为与HASHBYTES('SHA1', @input). 但是，一个优点CHECKSUM()是它的输入大小没有（明显的）限制。

    其次，如果你使用HASHBYTES()我推荐使用SHA1作为你的哈希算法。在可用选项SHA1中，碰撞率最低，并且速度不是您的用例关心的问题。

    最后，要HASHBYTES()针对大于 8000 字节的输入使用，您必须：

    1. 将您的输入分成 8000 字节的块。
    2. 散列每个块。

    3. 以某种方式组合生成的哈希值并对它们进行哈希处理以获得最终输出。

      您可以通过以下两种方式之一执行此操作：

      1. 将您的哈希输出转换为字符串，将它们连接起来，然后对结果进行哈希处理。
      2. 将所有哈希输出粘贴到内存表中，并使用CHECKSUM_AGG().

    4. 将此工作封装为一个以输入为输入的函数NVARCHAR(MAX)。

    综上所述，直接使用gbn 建议的方式比较 proc 定义，或者像Mike 建议OBJECT_DEFINITION()的那样，随心所欲地将所有定义推送到任何地方都更简单。

    我想知道什么样的环境会从只部署更改的过程并使用散列以避免复制和比较完整定义的过程中显着受益。你需要有很多程序来保持同步。

    • 3

  3. 我想说这里最好的答案是尽可能频繁地删除/重新创建 - 只编写权限并运行它。您可以根据需要多次运行权限脚本，它不会出错。

    当您直接删除并重新创建并运行您的权限脚本时，尝试提出一种有效、安全和可靠的散列方法来仅修改受影响的程序并不是真正必要的。

    您会发现很多商店都以这种方式进行部署。

    • 2

相关问题