具有可选参数的存储过程,该参数在SQL Server Management Studio“执行存储过程”或“脚本存储过程”中不显示

您可以用另一个存储过程包装您的存储过程:

CREATE PROCEDURE dbo.my_orig_proc
    @id INT
   ,@some_param_default INT = 10

AS
BEGIN
...
END

包装:

CREATE PROCEDURE dbo.my_wrapper_proc
   @id INT
 AS
 BEGIN
     EXEC dbo.my_orig_proc @id;
 END

另一种方法是添加检查,不允许特定用户覆盖值:

CREATE PROCEDRUE dbo.my_orig_proc
   @id INT,
   ,@some_param_default INT = 10
AS
BEGIN
   IF USER_NAME() = 'user_name' AND @some_param_default <> 10
      RAISERROR('You cannot change @some_param_default value' ,16,1);
END