这是我参与8月更文挑战的第24天，活动详情查看：8月更文挑战

此处之所以说sp_procoption是必须了解的，原因是它可以设置其他的存储过程在数据库实例启动或重启时，自动执行。

这样，几乎就可以实现自动启用任何想要的设置功能或脚本。实现类似Agent代理的功能。

比如之前介绍的跟踪标记，对于某些需要一直开启的跟踪标记，除了在SQL Server的启动参数中添加要启用的标记之外；就可以利用sp_procoption存储过程，做到在数据库实例重启后，仍然可以自动启用跟踪标记。

还有一些需要自动启动的任务，也可以考虑使用sp_procoption。当然最常用的还是通过 Agent 代理的 Job 作业实现，尤其是需要定时、定期循环执行的任务。

介绍sp_procoption

sp_procoption过程用于设置或清除一个存储过程在SQL Server示例每次启动时自动执行。

语法：

exec sp_procoption @ProcName = ['stored procedure name'], 
@OptionName = 'STARTUP', 
@OptionValue = [on|off]

• ProcName——是存储过程名称
• OptionName——是选项名（目前，只允许一个值，即'STARTUP'）
• OptionValue – 是否开启的值。（ON 或 OFF）/（1 或 0）/（TRUE 或 FALSE）/（YES 或 NO）

该存储过程执行的结果，返回：0（成功）或错误号（失败）。

sp_procoption使用限制

1. 启动的存储过程必须在master数据库并架构为DBO

USE AdventureWorks2016
CREATE TABLE TestStartup(ID int,logDate datetime);
go
Create or Alter procedure test_startup1
as
begin
	begin transaction T1;
	insert into TestStartup values(1,GETDATE());
	commit transaction T1;
end
go
exec sp_procoption @ProcName='test_startup1',@OptionName='STARTUP', @OptionValue=1;

执行报错

Msg 15398, Level 11, State 1, Procedure sp_procoption, Line 73 [Batch Start Line 11]
Only objects in the master database owned by dbo can have the startup setting changed.

2. 它允许 SQL 过程/CLR 过程/扩展过程

3. 启动的过程不能包含有参数，INPUT 或 OUTPUT 参数都不行。

USE master;
go
Create or Alter procedure test_startup1(@val int)
as
begin
	begin transaction T1;
	insert into AdventureWorks2016.dbo.TestStartup values(1,GETDATE());
	commit transaction T1;
end
go
exec sp_procoption @ProcName='test_startup1',@OptionName='STARTUP', @OptionValue=1;

执行报错如下：

Msg 15399, Level 11, State 1, Procedure sp_procoption, Line 90 [Batch Start Line 13]
Could not change startup option because this option is restricted to objects that have no parameters.

4. 当所有数据库都恢复时开始执行存储过程，并且在启动时会记录“恢复已完成”(Recovery is completed)消息

5. 需要sysadmin权限。

查看随实例自动启动的存储过程

sp_procoption执行后会设置对象的属性ExecIsStartup为1。可以通过 OBJECTPROPERTY 函数获取对象的属性值并进行验证。

因此，使用下面的语句，可以查找哪些存储过程是自动启动的。

-- 查看随实例自动启动的存储过程
select routine_name
from master.INFORMATION_SCHEMA.ROUTINES
where OBJECTPROPERTY(OBJECT_ID(routine_name),'ExecIsStartup')=1;

参考

• sp_procoption in sql server