SQL Server - sp_procoption marking procedure with parameters -

let's assume have simple logging procedure run automatically during sql server restart:

use master go  create table dbo.silly_logging(id int identity(1,1) primary key                                ,created_date datetime default getdate()                                ,comment varchar(100)); go  -- no parametrs create procedure dbo.my_procedure  insert dbo.silly_logging(comment) values ('sql server startup'); go  -- mark procedure start @ sql server instance startup exec sp_procoption @procname = 'dbo.my_procedure'     , @optionname = 'startup'        , @optionvalue = 'on';  select name, is_auto_executed master.sys.procedures is_auto_executed = 1; -- my_procedure 1  -- restart instance select * dbo.silly_logging; --id    created_date    comment --1 2017-07-28 07:01:24.650 sql server startup 

if tried same procedure parameters error:

create procedure dbo.my_procedure2 @i int = 10 select @i; go  -- mark procedure start @ sql server instance startup exec sp_procoption @procname = 'dbo.my_procedure2'     , @optionname = 'startup'        , @optionvalue = 'on'; 

msg 15399, level 11, state 1, procedure sp_procoption,

could not change startup option because option restricted objects have no parameters.

but still alter existing procedure:

alter procedure dbo.my_procedure @text nvarchar(100) = 'default value' insert dbo.silly_logging(comment) values (@text); go  -- restart instance select * dbo.silly_logging; --id    created_date    comment --1 2017-07-28 07:01:24.650 sql server startup --2 2017-07-28 07:03:50.510 default value 

now end stored procedure parameter (default value) provided.

drawback: cannot turned off .

exec sp_procoption @procname = 'dbo.my_procedure'     , @optionname = 'startup'        , @optionvalue = 'off'; 

msg 15399, level 11, state 1, procedure sp_procoption not change startup option because option restricted objects have no parameters.

---

of course if use drop-create work expected.

• drop procedure - object removed, execatstartup property removed too
• create procedure default parameter
• exec sp_procoption - return error
• procedure won't fired

but create-alter path is:

• create procedure without parameter
• exec sp_procoption (procedure start @ startup)
• alter procedure default parameter
• procedure fired

is there specific reason why work way? specifically, why while altering object property (like execatstartup) not validated?

sp_procoption explicitly checks procedure valid:

• owned dbo
• in master
• having no parameters

before anything.

the effect of sp_procoption 'proc', 'startup', 'on', after passing these checks, set object property execisstartup 1 (and off sets 0). can verify objectproperty.

there no particular reason why alter procedure couldn't check property , validate procedure has no parameters , rejecting alter otherwise, doesn't. result, can mark procedure startup, alter have parameters, , run sp_procoption refusing handle anymore, if turn option off.

if feature implemented today, own syntax (alter procedure foo set startup = on) rather internal stored procedure, microsoft moving away (compare sp_dbcmptlevel versus alter database set compatibility_level, sp_attach_db versus create database attach). had been part of alter proper, have made logical alter procedure have check on parameters if procedure marked startup. 1 of things that's not high on anyone's priority list feature improvements, though.

the workaround, of course, simple enough: if must call procedure (default) parameters startup procedure, wrapper stored procedure has no parameters , forwards call. because startup procedures can royally screw server if they're not written, in fact idea use them only startup (and give them names startup) , not have them pull double duty in setting parameters it's easy change them inadvertently.