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

In SQL Server Management Studio (SSMS), you can execute a stored procedure using the GUI for assistance. This can done by right-clicking on the stored procedure in the Object Explorer and selecting either "Execute Stored Procedure" or "Script Stored Procedure as > CREATE to > New Query Editor Window".

在SQL Server Management Studio (SSMS)中，您可以使用GUI来执行存储过程。通过右键单击Object Explorer中的存储过程，并选择“执行存储过程”或“作为>创建到>新查询编辑器窗口的脚本存储过程”。

Both of these result in a pre-built SQL query to execute the SP, and both of them include optional parameters from the SP. Is there a way to make it so the optional parameters are "hidden"?

这两种结果都在预先构建的SQL查询中执行SP，并且它们都包含来自SP的可选参数，是否有方法使其成为可选参数，从而“隐藏”?

I have some user support folks who use SSMS to run certain SPs, and I don't want them providing values for certain optional parameters. I want to be able to provide them myself, if needed, when I run the SP, but not the user support people.

我有一些使用SSMS运行某些SPs的用户支持人员，我不希望他们为某些可选参数提供值。如果需要，我希望在运行SP时能够自己提供它们，而不是用户支持人员。

I've tagged SQL Server 2014 and 2008 R2 in case there's some option I can set in the SP itself.

我标记了SQL Server 2014和2008 R2，以防在SP本身中设置一些选项。

1 个解决方案

#1

You could wrap your stored procedure with another:

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

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

AS
BEGIN
...
END

Wrapper:

包装:

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

I would also restrict access to orignal procedures if necessary.

如果必要的话，我还会限制进入原始程序。

Another way is to add check and don't allow specific user to override value:

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

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

Drawback: You need to change parameter value in two places and if user has impersonate privilige he still can execute it.

缺点:您需要在两个地方更改参数值，如果用户已经模拟了privilige，他仍然可以执行它。

#1